Google Sheets formulas for maps, addresses and routes

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

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

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

The nearest street address for coordinates.

Convert coordinates between decimal and degrees/minutes/seconds.

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

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

The city or town. Alias: =TOWN.

The county / district.

The state / region. Alias: =REGION.

The country.

Road distance between two places: the miles a car drives. Add "straight" for as the crow flies.

Real road travel time between two places, in minutes.

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

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

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

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

The places of a category around one point: name, distance and address, nearest first.

How many places of a category sit within a radius of every row — competitor density, amenity scores, site scorecards.

The nearest place of a kind to every row, with the distance — nearest station, car park, hospital, motorway junction.

Crimes recorded within about 250 m of each UK address in the latest published month (police.uk), optionally one type. UK only for now.

England: Environment Agency Flood Map for Planning (Zone 3 high, Zone 2 medium, Zone 1 low). US: FEMA flood zone (A and V zones are high-risk Special Flood Hazard Areas).

The FEMA National Risk Index rating for the census tract each address sits in: Very Low, Relatively Low, Relatively Moderate, Relatively High or Very High. US only.

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

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

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

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

A tap-to-navigate Waze link per row.

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

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.

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.

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

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

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

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