How far is each customer from office: A Google Sheets distance formula recipe

To calculate how far each customer is from your office, paste your office address into an anchor cell (e.g., B1), use =GEOCODE() to get customer coordinates, and apply =DISTANCE(B1, C2:C200) down a column. Finally, use =SORT_BY_DISTANCE(A1:E200, B1, F1:F200) to generate a sorted view of the sheet.

This setup is for field service dispatchers, route planners, and local sales teams managing 200-home territories. By relying on an anchor cell and a dedicated sorting formula, you produce a non-destructive list of the closest clients, allowing crews to prioritise visits based on actual mileage rather than postcode guesswork.

Prerequisites: Setting up your anchor cell and customer list

Open Google Sheets and install the InstaMaps add-on. Once installed, access the tool by clicking Extensions > InstaMaps > Enable formulas. This opens a sidebar with a Build-the-workflow button that writes formula chains automatically, but manual typing provides exact control over your specific layout. The free tier permits 100 lookups per day, which increases to 1,000 lookups per day when you apply a free email unlock.

To measure physical distances, InstaMaps requires coordinates rather than raw text addresses. Designate cell B2 for your primary office location, typing the full address as a string (e.g., '10 Downing Street, London'). Next, list your customer addresses in column C, spanning from C2 down to C50.

You must convert these text addresses into latitude and longitude pairs. In cell D2, type =GEOCODE($B$2) to process the anchor location. In cell E2, apply =GEOCODE(C2:C50) to process the entire customer column. The function automatically spills the coordinate pairs into the adjacent rows. If your list contains formatting errors, such as missing postcodes or misspelt street names, wrap the range in the =CLEAN_ADDRESS(C2:C50) function first to standardise the text before running the geocode operation.

Keep your data structured strictly in columns. Column A holds the customer names, Column B holds the office anchor, Column C holds the customer addresses, and Column D holds the output. Ensuring no blank rows exist between C2 and C50 prevents coordinate misalignment when you apply formulas in subsequent steps.

Step-by-step: How far is each customer from office

With coordinates established, calculate the exact linear distance from the office to each customer. InstaMaps uses the =DISTANCE() custom function to measure the straight-line geometry between two points, ignoring road layouts.

The syntax requires two arguments: an origin and a destination. Because your office location remains constant for every row in the dataset, you must lock the reference to its coordinate cell using absolute referencing (dollar signs). Assuming your office coordinates are in cell E2, and your first customer's coordinates are in cell D2, the formula is =DISTANCE($E$2, D2).

1. Enter the formula =DISTANCE($E$2, D2) into cell F2.

2. Press Enter. The cell outputs a numeric value representing the distance. The output unit depends on your sheet's regional locale settings (miles for the UK, kilometres for other regions).

3. Click the small blue square at the bottom-right corner of cell F2.

4. Drag the fill handle down to F50.

Creating a nearest-neighbour view with =SORT_BY_DISTANCE

Standard spreadsheet sorting disrupts row alignment. If you highlight your customer database and sort column F to find the nearest client, you risk detaching addresses from their corresponding contact names and phone numbers. The =SORT_BY_DISTANCE() function solves this by generating a separate, dynamic view.

Instead of rearranging your existing rows, create a new tab. Use =SORT_BY_DISTANCE() to output a newly sorted array based on the distance variable. The syntax requires the anchor coordinate cell and the entire data range you want to evaluate. If your original unsorted data sits in A2:E50, and your office coordinates remain locked in E2, type =SORT_BY_DISTANCE($E$2, A2:E50) into cell A1 of the new tab.

This function evaluates the geometry of the dataset against the anchor point and outputs the entire range ordered from shortest distance to longest. It leaves your original sheet untouched. If you change an address in the original tab from 'Manchester' to 'Leeds', the sorted view on the second tab updates automatically without requiring a manual re-sort.

This approach creates a dedicated nearest-neighbour list without altering your underlying database. You can link this sorted output to the =INSTAMAP() function to generate a live, hosted, shareable map URL that visually plots the stops in exact proximity order. The map updates instantly when the sheet changes. Because the second tab only contains an array formula, users cannot manually overwrite a customer's phone number while trying to organise the schedule.

Worked example: Planning 47 stops for 5 crews across a 200-home farm

Consider a rural property portfolio containing 200 homes. A maintenance company operates from a single depot and must dispatch 5 crews to 47 active service tickets on a specific Tuesday morning.

1. Column A: Ticket ID (spanning T-401 down to T-447)

2. Column B: Customer Property Address

3. Column C: =GEOCODE(B2:B48)

4. Column D: =DISTANCE($C$2, C2:C48)

Sharing results via live maps and routing links

Once you have your sorted distance list, you need to share it or navigate it. To generate a hosted, shareable map for your dataset, use `=INSTAMAP(B2:B150, D2:D150)` in an empty cell, where column B holds the addresses and column D holds the distance calculations. The formula returns a live, hosted URL. If you add a new customer to row 151, the hosted map at that URL updates automatically when the sheet changes. You can send this link to staff or drivers without granting them edit access to the underlying Google Sheet.

To push the nearest customers directly into navigation, use `=ROUTE_LINK()`. If your `=SORT_BY_DISTANCE()` view outputs the ten closest addresses to cells C2:C11, applying `=ROUTE_LINK(C2:C11)` creates a standard Google Maps URL. Tapping this link on a mobile device opens Google Maps with the route pre-loaded. Note the strict limit: `=ROUTE_LINK()` uses Google Maps' official URL scheme, which allows a maximum of 11 stops. If you attempt to link cells C2:C20 (19 stops), the formula returns an error rather than truncating the route or silently dropping stops. For a 47-stop route, you must break your data into separate `=ROUTE_LINK()` formulas.

Instead of manually typing these formulas, navigate to Extensions > InstaMaps > Enable formulas to open the sidebar. You can highlight your data range and click the Build-the-workflow button to automatically insert the `=INSTAMAP()` or `=ROUTE_LINK()` chains based on your selected columns.

Limits and honest alternatives

InstaMaps operates on a free tier with a limit of 100 lookups per day. You can increase this to 1,000 lookups per day with a free email registration. When you exceed your daily quota, cells running `=DISTANCE()` or `=GEOCODE()` return a quota error instead of calculating. If your customer list has 1,500 rows, a single `=DISTANCE()` pull against your anchor cell will process the first 1,000 rows and fail on the remainder until midnight. You must calculate the remaining rows the next day.

If you prefer visual interfaces over spreadsheet formulas, Google My Maps is a viable manual alternative. You can import a CSV of customer addresses directly into Google My Maps, which plots them as pins. However, My Maps lacks a distance calculation function. You must measure distances manually using the drawing tools, which becomes unmanageable past 50 addresses. For lists requiring continuous bulk distance checks against a changing anchor cell, the Google Sheets formula approach is faster. For users who only need to plot 20 to 30 points on a map once a quarter without calculating exact distances, Google My Maps remains the better, zero-formula choice.

Who this workflow is for

The InstaMaps distance workflow suits specific operational scales. Consider your needs against the following profiles before implementing it:

1. **Local delivery dispatchers:** If you manage a 200-home farm and need to assign the nearest stops to a driver each morning, the `=DISTANCE()` and `=ROUTE_LINK()` combination removes manual map-checking. It creates a static snapshot based on your sheet data.

2. **Regional service managers:** If you oversee 5 crews and need to verify which customer is closest to a specific technician's anchor location (such as a home office or depot), `=SORT_BY_DISTANCE()` provides immediate, sortable distance metrics. This allows you to group territories by a strict radius without learning complex GIS software.

3. **Live fleet trackers (Skip this):** This workflow calculates distances based on static addresses in a spreadsheet. If you require real-time GPS tracking, dynamic traffic-based rerouting, or vehicle telemetry, you need enterprise fleet-tracking software, not a Google Sheets add-on.

4. **High-volume routing engines (Skip this):** If you need to optimise a 47-stop travelling salesman problem with live traffic data, `=ROUTE_LINK()` will fail. It hard-caps at 11 stops and does not calculate the most efficient mathematical sequence. For complex optimised multi-stop routing, use dedicated routing software.

Common Questions