Google Sheets formulas for maps, addresses and routes

30 free Google Sheets formulas that geocode, clean, measure, route, publish and report — straight from your spreadsheet. They work like native formulas: type one, fill a whole column.

Cheat sheet

One line each. Ranges everywhere an address goes: =GEOCODE(A2:A50) fills fifty rows.

Geocode & clean

=GEOCODE(address)	Latitude and longitude for an address, a range, or Plus Codes.
=CLEAN_ADDRESS(address)	The standardised, geocoder-verified version of a messy address.
=REVERSE_GEOCODE(lat, lng)	The nearest street address for coordinates.
=COORD_CONVERT(coords, to?)	Convert coordinates between decimal and degrees/minutes/seconds.
=GEOCODE_IP(ip)	City, country and coordinates for an IP address (or range of them).

Fill address columns

=POSTCODE(address)	The postcode / ZIP. Aliases: =ZIP, =ZIPCODE.
=CITY(address)	The city or town. Alias: =TOWN.
=COUNTY(address)	The county / district.
=STATE(address)	The state / region. Alias: =REGION.
=COUNTRY(address)	The country.

Distances & questions

=DISTANCE(from, to, unit?)	Straight-line distance between two places.
=TRAVEL_TIME(from, to, mode?)	Real road travel time between two places, in minutes.
=DISTANCE_MATRIX(origins, destinations, mode?, unit?)	Every-to-every road distances (or times): origins down, destinations across.
=CLOSEST_TO(anchor, candidates, n?)	The nearest location(s) to an anchor, from a list.
=WITHIN_RADIUS(anchor, candidates, radius, unit?)	Everything from a list inside a radius, sorted nearest-first.
=SORT_BY_DISTANCE(range, anchor, unit?)	Your rows re-sorted nearest-first, with a distance column added.

Run the day

=GEOCODE_WORKFLOW(stops, start?, end?)	The whole route day in one formula: coordinates, best visiting order, and one Google Maps route link.
=VISIT_ORDER(stops, start?, end?)	The best visiting order (1, 2, 3…) aligned to your rows — on real routes this cuts 20–50% of the miles.
=ROUTE_LINK(stops, mode?)	One Google Maps link for the whole run — up to 100 stops (10x Google's usual 10).
=WAZE_LINK(address)	A tap-to-navigate Waze link per row.
=TERRITORY(addresses, reps, balanceBy?)	Splits a list into balanced geographic territories, one per rep.
=LOGBOOK(mapLink, limit?, refresh?, override?)	The full visit journal, newest first: every check-off ever, like a bank statement. Answers "what happened, in order?"
=VISIT_STATUS(range, mapLink, refresh?, override?)	One row per customer, aligned with YOUR list: their current status only, like an account balance. Answers "where does each customer stand?"

Publish & share

=INSTAMAP(range, title?)	A hosted, live, shareable map from your rows — the URL lands in the cell.
=EMBED_MAP(range, siteName)	A white-label embed link for your website: no branding, light theme, your name on the loading screen.
=STREETVIEW_LINK(address)	A Google Street View link per row — tap to look at the building.

get-instamaps.com/formulas · free Google Sheets™ add-on: workspace.google.com/marketplace/app/instamaps

Full reference

Geocode & clean

=GEOCODE(address)

The workhorse: coordinates for anything address-shaped. Handles street addresses, postcodes, landmarks, and Plus Codes — both full (772CH947+CV) and short with a locality (H963+HV Maracaibo).

Argument	What it takes
address	An address, or a range of addresses (=GEOCODE(A2:A50) fills every row).

=GEOCODE(A2)

=GEOCODE(A2:A50)

Ranges above 400 rows: use Extensions → InstaMaps → Add coordinates to this sheet (no formula time limits).

=CLEAN_ADDRESS(address)

The standardised, geocoder-verified version of a messy address.

Argument	What it takes
address	An address, or a range of addresses (=GEOCODE(A2:A50) fills every row).

=CLEAN_ADDRESS(A2:A50)

=REVERSE_GEOCODE(lat, lng)

The nearest street address for coordinates.

Argument	What it takes
lat	Latitude — or a single "lat,lng" string as the only argument.
lng	Longitude.

=REVERSE_GEOCODE(51.5007, -0.1246)

=REVERSE_GEOCODE(A2)

=COORD_CONVERT(coords, to?)

Convert coordinates between decimal and degrees/minutes/seconds.

Argument	What it takes
coords	The coordinates to convert (either format).
to	Optional target: "dms" (default) or "decimal".

=COORD_CONVERT("51.5007, -0.1246")

=COORD_CONVERT(A2, "decimal")

=GEOCODE_IP(ip)

City, country and coordinates for an IP address (or range of them).

Argument	What it takes
ip	An IP address, or a range of IPs.

=GEOCODE_IP(A2:A50)

Fill address columns

=POSTCODE(address)

The postcode / ZIP. Aliases: =ZIP, =ZIPCODE.

Argument	What it takes
address	An address, or a range of addresses (=GEOCODE(A2:A50) fills every row).

=POSTCODE(A2:A50)

=CITY(address)

The city or town. Alias: =TOWN.

Argument	What it takes
address	An address, or a range of addresses (=GEOCODE(A2:A50) fills every row).

=CITY(A2:A50)

=COUNTY(address)

The county / district.

Argument	What it takes
address	An address, or a range of addresses (=GEOCODE(A2:A50) fills every row).

=COUNTY(A2:A50)

=STATE(address)

The state / region. Alias: =REGION.

Argument	What it takes
address	An address, or a range of addresses (=GEOCODE(A2:A50) fills every row).

=STATE(A2:A50)

=COUNTRY(address)

The country.

Argument	What it takes
address	An address, or a range of addresses (=GEOCODE(A2:A50) fills every row).

=COUNTRY(A2:A50)

Distances & questions

=DISTANCE(from, to, unit?)

Straight-line distance between two places.

Argument	What it takes
from	An address or "lat,lng".
to	An address or "lat,lng".
unit	Optional: "km" (default) or "mi".

=DISTANCE(A2, B2, "mi")

=TRAVEL_TIME(from, to, mode?)

Real road travel time between two places, in minutes.

Argument	What it takes
from	An address or "lat,lng".
to	An address or "lat,lng".
mode	Optional: driving (default), walking, cycling.

=TRAVEL_TIME(A2, B2)

=TRAVEL_TIME(A2, B2, "walking")

=DISTANCE_MATRIX(origins, destinations, mode?, unit?)

Every-to-every road distances (or times): origins down, destinations across.

Argument	What it takes
origins	Addresses down the side (up to 25).
destinations	Addresses across the top (up to 25).
mode	Optional: driving (default), walking, cycling.
unit	Optional: "km" (default), "mi", or "min" for travel time.

=DISTANCE_MATRIX(A2:A10, C2:C6, "driving", "min")

=CLOSEST_TO(anchor, candidates, n?)

The nearest location(s) to an anchor, from a list.

Argument	What it takes
anchor	The reference address (or "lat,lng").
candidates	The list to search (e.g. A2:A200).
n	Optional: how many nearest to return (default 1).

=CLOSEST_TO(B1, A2:A200)

=CLOSEST_TO(B1, A2:A200, 5)

=WITHIN_RADIUS(anchor, candidates, radius, unit?)

Everything from a list inside a radius, sorted nearest-first.

Argument	What it takes
anchor	The centre address (or "lat,lng").
candidates	The list to search.
radius	The radius number.
unit	Optional: "km" (default) or "mi".

=WITHIN_RADIUS(B1, A2:A200, 25, "mi")

=SORT_BY_DISTANCE(range, anchor, unit?)

Your rows re-sorted nearest-first, with a distance column added.

Argument	What it takes
range	The rows to sort (address in any column).
anchor	The reference address (or "lat,lng").
unit	Optional: "km" (default) or "mi".

=SORT_BY_DISTANCE(A2:C50, "Manchester")

Run the day

=GEOCODE_WORKFLOW(stops, start?, end?)

The three-formula chain as a single spill: per row you get Lat, Lng and Visit order; the first row also carries the Route link for the whole run in visiting order (up to 100 stops).

Argument	What it takes
stops	Your stops (2–100), e.g. A2:A101. Plus Codes work.
start	Optional starting address (default: the first row).
end	Optional end point: the run finishes at the row nearest this address, or gets a final leg back to it (your depot). Same address for start and end = a round trip.

=GEOCODE_WORKFLOW(A2:A26)

=GEOCODE_WORKFLOW(A2:A26, "The depot, Salford")

=VISIT_ORDER(stops, start?, end?)

The best visiting order (1, 2, 3…) aligned to your rows — on real routes this cuts 20–50% of the miles.

Argument	What it takes
stops	The stops to order.
start	Optional starting address (default: the first row).
end	Optional end point: the run finishes at the row nearest this address, or gets a final leg back to it (your depot). Same address for start and end = a round trip.

=VISIT_ORDER(B2:B26)

=VISIT_ORDER(B2:B26, "The depot, Salford")

=ROUTE_LINK(stops, mode?)

One Google Maps link for the whole run — up to 100 stops (10x Google's usual 10).

Argument	What it takes
stops	Addresses in visit order (e.g. A2:A26).
mode	Optional: driving (default), walking, cycling, transit (modes apply up to 11 stops).

=ROUTE_LINK(A2:A26)

Google Maps itself allows 10. The documented URL trick gets 25. This link routes 100 — we tested it on web and mobile web.

=WAZE_LINK(address)

A tap-to-navigate Waze link per row.

Argument	What it takes
address	An address, or a range (one link per row).

=WAZE_LINK(A2:A50)

=TERRITORY(addresses, reps, balanceBy?)

Splits a list into balanced geographic territories, one per rep.

Argument	What it takes
addresses	The addresses to split.
reps	A number of territories, or a range of rep names.
balanceBy	Optional value column to balance by (e.g. revenue).

=TERRITORY(A2:A500, 4)

=TERRITORY(A2:A500, D2:D5, C2:C500)

=LOGBOOK(mapLink, limit?, refresh?, override?)

Pairs with the map's Logbook page: your crew checks off stops on their phones, this formula pulls the dated history back into the sheet — every event, repeat visits included, on its own tab. For "where does each customer stand right now?", use =VISIT_STATUS instead: same data, one current row per customer.

Argument	What it takes
mapLink	Your map link from =INSTAMAP, or the map id.
limit	Optional: number of entries (default 200, max 500).
refresh	Optional: point at a refresh cell — any change to it refetches. "Set up visit tracking" wires this automatically (and bumps it as you work); ⚡ Live sync turns it into a count that changes on every check-off. NOW() is not allowed as a custom-function argument.
override	Optional: point at a spare cell; change that cell to refetch instantly.

=LOGBOOK($M$3)

=LOGBOOK($M$3, 200, $D$1)

=VISIT_STATUS(range, mapLink, refresh?, override?)

The other projection of the same data as =LOGBOOK: where the journal is time-ordered history (many rows per stop), this is customer-ordered state (exactly one row each, beside your data). Put it next to your customer list and the latest check-off lands on the right row by itself.

Argument	What it takes
range	Your client rows (first cell = the name, same as =INSTAMAP).
mapLink	Your map link from =INSTAMAP, or the map id.
refresh	Optional: point it at COUNTA over your =LOGBOOK journal — each new check-off changes the count, which refetches the statuses. Start the range at column B of the spill: Sheets blocks custom functions from touching the NOW() cell itself.
override	Optional: a spare cell for instant manual refetch.

=VISIT_STATUS(A2:A50, $M$3, COUNTA(Logbook!$B$3:$F))

Publish & share

=INSTAMAP(range, title?)

The map updates itself when the sheet changes. Same title = same map, updated in place, so the link you share stays good forever.

Argument	What it takes
range	The rows to map (first cell of each row becomes the pin name).
title	Optional map title — also its stable identity.

=INSTAMAP(A2:D50, "Client visits")

=EMBED_MAP(range, siteName)

A white-label embed link for your website: no branding, light theme, your name on the loading screen.

Argument	What it takes
range	Rows to map (first cell = the pin name).
siteName	Your site's name — shows while the map loads, and pins the map's identity.

=EMBED_MAP(A2:C50, "Palouse Mindfulness")

Paste the returned link into an iframe: <iframe src="THE_LINK" width="100%" height="600" style="border:0"></iframe>

=STREETVIEW_LINK(address)

A Google Street View link per row — tap to look at the building.

Argument	What it takes
address	An address, or a range of addresses (=GEOCODE(A2:A50) fills every row).

=STREETVIEW_LINK(A2:A50)

All of it, free, inside Google Sheets™

Install InstaMaps