Every location formula for Google Sheets

29 formulas that geocode, clean, measure, route, publish and report — straight from your spreadsheet. Free with the InstaMaps add-on.

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

=VISIT_ORDER(stops, start?)	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 25 stops (past 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 map's visit journal as rows: when, stop, status, driver, note.
=VISIT_STATUS(range, mapLink, refresh?, override?)	The latest check-off per client, side by side with your rows: status, when, driver, note.

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

=VISIT_ORDER(stops, start?)

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).

=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 25 stops (past 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)

Longer than 25? Split into legs: =ROUTE_LINK(A2:A26) then =ROUTE_LINK(A26:A50).

=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.

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: pass NOW() and set File → Settings → Calculation to "On change and every minute" — the journal then refreshes itself every minute.
override	Optional: point at a spare cell; change that cell to refetch instantly.

=LOGBOOK($M$3)

=LOGBOOK($M$3, 200, NOW(), $O$1)

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

The latest check-off per client, side by side with your rows: status, when, driver, note.

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: NOW() for auto-refresh (see =LOGBOOK).
override	Optional: a spare cell for instant manual refetch.

=VISIT_STATUS(A2:A50, $M$3, NOW(), $O$1)

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