# Google Sheets Formulas for Maps, Addresses and Routes (Free Reference) | InstaMaps > 36 free Google Sheets formulas for anything location: geocode addresses, clean messy data, measure distance and drive time, order routes up to 100 stops, and publish a live map. Complete reference with a printable cheat sheet. Source: https://get-instamaps.com/formulas [IMInstaMaps](/)[ Get the add-on free ](https://workspace.google.com/marketplace/app/instamaps/103204565785?utm%5Fsource=get-instamaps&utm%5Fmedium=site&utm%5Fcampaign=formulas-page) # Google Sheets formulas for maps, addresses and routes 36 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)](#GEOCODE) | Latitude and longitude for an address, a range, or Plus Codes. | | -------------------------------------------------- | ------------------------------------------------------------------- | | [\=CLEAN\_ADDRESS(address)](#CLEAN%5FADDRESS) | The standardised, geocoder-verified version of a messy address. | | [\=REVERSE\_GEOCODE(lat, lng)](#REVERSE%5FGEOCODE) | The nearest street address for coordinates. | | [\=COORD\_CONVERT(coords, to?)](#COORD%5FCONVERT) | Convert coordinates between decimal and degrees/minutes/seconds. | | [\=GEOCODE\_IP(ip)](#GEOCODE%5FIP) | City, country and coordinates for an IP address (or range of them). | ### Fill address columns | [\=POSTCODE(address)](#POSTCODE) | The postcode / ZIP. Aliases: =ZIP, =ZIPCODE. | | -------------------------------- | -------------------------------------------- | | [\=CITY(address)](#CITY) | The city or town. Alias: =TOWN. | | [\=COUNTY(address)](#COUNTY) | The county / district. | | [\=STATE(address)](#STATE) | The state / region. Alias: =REGION. | | [\=COUNTRY(address)](#COUNTRY) | The country. | ### Distances & questions | [\=DISTANCE(from, to, unit?, mode?)](#DISTANCE) | Road distance between two places: the miles a car drives. Add "straight" for as the crow flies. | | ----------------------------------------------------------------------------- | ----------------------------------------------------------------------------------------------- | | [\=TRAVEL\_TIME(from, to, mode?)](#TRAVEL%5FTIME) | Real road travel time between two places, in minutes. | | [\=DISTANCE\_MATRIX(origins, destinations, mode?, unit?)](#DISTANCE%5FMATRIX) | Every-to-every road distances (or times): origins down, destinations across. | | [\=CLOSEST\_TO(anchor, candidates, n?)](#CLOSEST%5FTO) | The nearest location(s) to an anchor, from a list. | | [\=WITHIN\_RADIUS(anchor, candidates, radius, unit?)](#WITHIN%5FRADIUS) | Everything from a list inside a radius, sorted nearest-first. | | [\=SORT\_BY\_DISTANCE(range, anchor, unit?)](#SORT%5FBY%5FDISTANCE) | Your rows re-sorted nearest-first, with a distance column added. | ### What's around it (UK) | [\=NEARBY(anchor, category, n?, radius?)](#NEARBY) | The places of a category around one point: name, distance and address, nearest first. | | ------------------------------------------------------------------- | --------------------------------------------------------------------------------------------------------------------- | | [\=COUNT\_NEARBY(range, category, radius?, unit?)](#COUNT%5FNEARBY) | How many places of a category sit within a radius of every row — competitor density, amenity scores, site scorecards. | | [\=NEAREST(range, kind, unit?)](#NEAREST) | The nearest place of a kind to every row, with the distance — nearest station, car park, hospital, motorway junction. | ### Risk at each address | [\=CRIME(range, kind?)](#CRIME) | Recorded crime around every address — for underwriting, site selection and security planning. | | ----------------------------------- | ------------------------------------------------------------------------------------------------------- | | [\=FLOOD(range)](#FLOOD) | The official flood zone at every address, and whether it is high, medium or low risk. | | [\=HAZARD(range, hazard?)](#HAZARD) | FEMA natural-hazard risk at every US address — hurricane, wildfire, tornado, hail, earthquake and more. | ### Run the day | [\=GEOCODE\_WORKFLOW(stops, start?, end?)](#GEOCODE%5FWORKFLOW) | The whole route day in one formula: coordinates, best visiting order, and one Google Maps route link. | | ----------------------------------------------------------------------- | ------------------------------------------------------------------------------------------------------------------------------------------- | | [\=VISIT\_ORDER(stops, start?, end?)](#VISIT%5FORDER) | 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?)](#ROUTE%5FLINK) | One Google Maps link for the whole run — up to 100 stops (10x Google's usual 10). | | [\=WAZE\_LINK(address)](#WAZE%5FLINK) | A tap-to-navigate Waze link per row. | | [\=TERRITORY(addresses, reps, balanceBy?)](#TERRITORY) | Splits a list into balanced geographic territories, one per rep. | | [\=LOGBOOK(mapLink, limit?, refresh?, override?)](#LOGBOOK) | 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?)](#VISIT%5FSTATUS) | 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?)](#INSTAMAP) | A hosted, live, shareable map from your rows — the URL lands in the cell. | | ------------------------------------------------- | ----------------------------------------------------------------------------------------------------- | | [\=EMBED\_MAP(range, siteName)](#EMBED%5FMAP) | A white-label embed link for your website: no branding, light theme, your name on the loading screen. | | [\=STREETVIEW\_LINK(address)](#STREETVIEW%5FLINK) | 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?, mode?) Road distance between two places: the miles a car drives. Add "straight" for as the crow flies. | Argument | What it takes | | -------- | ---------------------------------------------------------------- | | from | An address or "lat,lng". | | to | An address or "lat,lng". | | unit | Optional: "km" (default) or "mi". | | mode | Optional: "straight" for straight-line distance instead of road. | \=DISTANCE(A2, B2, "mi") \=DISTANCE(A2, B2, "mi", "straight") \=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") ### What's around it (UK) \=NEARBY(anchor, category, n?, radius?) The places of a category around one point: name, distance and address, nearest first. | Argument | What it takes | | -------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | anchor | The point: an address or "lat,lng". | | category | What to look for: "pharmacy", "station", "supermarket", "school", "GP", "pub", "car park", "petrol station", "EV charging", "estate agent", "industrial estate" and more. | | n | Optional: how many to return (default 10, max 50). | | radius | Optional: search radius in km (default 2, max 25). | \=NEARBY(B1, "pharmacy") \=NEARBY(B1, "station", 3, 5) \=COUNT\_NEARBY(range, category, radius?, unit?) How many places of a category sit within a radius of every row — competitor density, amenity scores, site scorecards. | Argument | What it takes | | -------- | ------------------------------------------------------ | | range | Addresses or "lat,lng" cells, one per row (up to 500). | | category | What to count, e.g. "school", "supermarket", "pub". | | radius | Optional: radius number (default 2 km, max 25 km). | | unit | Optional: "km" (default) or "mi". | \=COUNT\_NEARBY(A2:A200, "school", 1) \=COUNT\_NEARBY(A2:A200, "supermarket", 0.5, "mi") \=NEAREST(range, kind, unit?) The nearest place of a kind to every row, with the distance — nearest station, car park, hospital, motorway junction. | Argument | What it takes | | -------- | --------------------------------------------------------- | | range | Addresses or "lat,lng" cells, one per row (up to 500). | | kind | What to find, e.g. "station", "school", "car park", "GP". | | unit | Optional: "km" (default) or "mi". | \=NEAREST(A2:A200, "station") \=NEAREST(A2:A200, "car park", "mi") ### Risk at each address \=CRIME(range, kind?) Crimes recorded within about 250 m of each UK address in the latest published month (police.uk), optionally one type. UK only for now. | Argument | What it takes | | -------- | -------------------------------------------------------------------------------------------------------------------------------------------------------- | | range | Addresses or "lat,lng" cells, one per row (up to 300). | | kind | Optional: "vehicle", "burglary", "violent", "theft", "shoplifting", "anti-social", "robbery", "drugs", "criminal damage" or "bike" (default: all crime). | \=CRIME(A2:A200) \=CRIME(A2:A200, "vehicle") \=FLOOD(range) 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). | Argument | What it takes | | -------- | ------------------------------------------------------ | | range | Addresses or "lat,lng" cells, one per row (up to 300). | \=FLOOD(A2:A200) \=HAZARD(range, hazard?) 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. | Argument | What it takes | | -------- | -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | range | US addresses or "lat,lng" cells, one per row (up to 300). | | hazard | Optional: "overall" (default), "hurricane", "wildfire", "tornado", "hail", "earthquake", "inland flood", "coastal flood", "heat wave", "winter weather", "strong wind", "lightning", "landslide" or "drought". | \=HAZARD(A2:A200) \=HAZARD(A2:A200, "hurricane") ### 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: \=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](https://workspace.google.com/marketplace/app/instamaps/103204565785?utm%5Fsource=get-instamaps&utm%5Fmedium=site&utm%5Fcampaign=formulas-page-footer)