Use the =DISTANCE() function from the InstaMaps add-on to calculate the straight-line distance between two addresses in Google Sheets. The formula accepts cell references containing addresses or coordinates and outputs a pure number in miles or kilometres, which you can sort and filter directly within your spreadsheet.
This function is designed for logistics coordinators managing a 200-home delivery farm, sales reps segmenting leads by proximity, or operations managers assigning 5 regional crews to 47 stops. The end state is a clean, sortable dataset of numerical distances that updates automatically when source addresses change, replacing manual mapping tasks.
- →=DISTANCE calculates the straight-line (as the crow flies) distance between two geographic points.
- →Outputs a clean numerical value in miles or kilometres, allowing you to sort and filter your location data natively.
- →Accepts exact coordinates or addresses (when paired with the =GEOCODE formula).
- →Bypasses the standard Google Sheets limitation of lacking a native mapping engine.
- →Subject to a free tier limit of 100 lookups per day (1,000/day with a free email unlock).
Syntax and Arguments
The =DISTANCE function calculates the straight-line (geodesic) distance between two geographic points. It outputs a raw numeric value, allowing you to sort, filter, or aggregate the data without stripping text or units.
Syntax
=DISTANCE(origin, destination, [unit])
origin (required): The starting location. This argument accepts a physical address string (e.g., "1600 Amphitheatre Pkwy, Mountain View, CA"), a postcode (e.g., "94043"), or decimal coordinates (e.g., "37.4220, -122.0841"). You can pass a single cell like A2 or an array.
destination (required): The target location. Accepts the exact same data types and formatting as the origin argument.
unit (optional): The measurement system for the output. Defaults to miles ("mi") if omitted. You must input "km" for kilometres, "m" for metres, or "ft" for feet. The input is not case-sensitive.
Return Value: The function returns a plain decimal number (e.g., 14.53). Because the output is numeric, it does not include text suffixes like "miles" or "km". This ensures the column remains sortable and usable in downstream mathematical formulas like =SUM() or =AVERAGE().
Coordinate Formatting: When passing coordinates directly into the origin or destination, separate latitude and longitude with a comma. If you are using the =GEOCODE() function in adjacent columns, you can nest the outputs directly: =DISTANCE(B2, C2), where B2 contains the origin coordinates and C2 holds the destination coordinates.
Worked Example: Routing 5 Crews Across a 200-Home Farm
Scenario: A regional farm management company oversees 200 distributed agricultural properties and needs to dispatch 5 maintenance crews from a central equipment depot to handle irrigation repairs.
1. Set up the dataset: Column A contains the 200 property addresses spanning A2:A201. Cell G2 contains the address of the central equipment depot (e.g., "4500 Agric Road, Reading").
2. Geocode the properties: To calculate distance accurately, convert the physical addresses into coordinates first. In cell B2, enter =GEOCODE(A2:A201). This single formula spills the latitude and longitude pairs down column B for all 200 properties.
3. Geocode the depot: In cell C2, enter =GEOCODE(G2) to output the central depot's coordinates.
4. Calculate straight-line mileage: In cell D2, write =DISTANCE(B2:B201, C2, "mi"). This returns an array of numeric mileages for all 200 properties measured against the depot.
5. Assign crews via filtering: Because the output in Column D is a pure number, you can now wrap it in a filter or use Google Sheets' native sorting. Select columns A through D, and apply Data > Create a filter. Sort Column D from smallest to largest.
6. Segment the work: The properties generating the lowest values in Column D (under 15 miles) are assigned to Crews 1 and 2. Properties between 15 and 30 miles go to Crews 3 and 4. Anything over 30 miles is assigned to Crew 5.
To automate the assignments based on the numeric output in Column D, add a standard logical formula in Column E. In E2, input: =IF(D2<=15, "Crew 1 or 2", IF(D2<=30, "Crew 3 or 4", "Crew 5")). Drag this down to E201.
If a farm manager adds a newly acquired property to row 202, they simply extend the formulas down: =GEOCODE(A202) in B202, =DISTANCE(B202, $C$2, "mi") in D202, and the IF statement in E202. The depot coordinate in C2 remains an absolute reference ($C$2), preventing errors as the formula evaluates down the 200 rows.
Common Errors and Troubleshooting
#NAME? Error
If your cell returns #NAME?=DISTANCE(...), Google Sheets does not recognise the function. This happens when the InstaMaps add-on is not installed on the active account. You must install the add-on from the Google Workspace Marketplace, then navigate to Extensions > InstaMaps > Enable formulas to authorise the script. For a broader breakdown of installation errors, read our google-sheets-geocode-formula troubleshooting guide.
Quota Exceeded Warnings
If the formula returns #ERROR! or a custom text string like "Daily quota exceeded", you have hit the lookup limit. The InstaMaps free tier allows 100 lookups per day. If you are calculating distances for 200 homes, you will exhaust this quota at row 101. To resolve this, use the free email verification feature inside the InstaMaps sidebar, which raises the daily limit to 1,000 lookups per day at no cost.
Invalid Coordinate Formatting
The =DISTANCE formula fails if the origin or destination inputs contain text that cannot be mapped or parsed. If you pass a malformed coordinate string, such as missing the negative sign on a longitude (51.5, 0.12 instead of 51.5, -0.12), the formula will calculate the distance to an incorrect point on the globe rather than throwing an error.
If the cell contains completely invalid data (e.g., "N/A" or a broken address that =GEOCODE failed to process), =DISTANCE will return #VALUE!. To prevent cascading #VALUE! errors across a 200-row sheet, wrap your distance formula in an IFERROR statement: =IFERROR(DISTANCE(B2, C2, "km"), "Check Address"). This flags broken rows without breaking your filter views or aggregate calculations.
Composes With: Building Routing Chains
InstaMaps custom functions handle array ranges natively, allowing you to pass the output of one formula directly into another. The InstaMaps sidebar (Extensions > InstaMaps > Enable formulas) includes a Build-the-workflow button designed to write these chains automatically, but understanding the underlying cell references ensures accurate scaling.
Chain 1: Sorting a 47-stop list by mileage. If you manage a delivery log with 47 stops listed in A2:A48 and your depot address in cell G1, calculating raw mileage for each stop requires =DISTANCE(A2:A48, G1). Viewing the resulting numbers is only half the task. To reorder the entire sheet based on those values, combine the distance calculation with =SORT_BY_DISTANCE(A2:A48, G1). This evaluates the straight-line distance from G1 to every cell in the range, then physically reorders the rows from shortest to longest. It replaces the manual process of calculating distances, copying them as static values, and applying Google Sheets' native sort range tool.
Chain 2: Mapping the nearest points. When you need a visual confirmation of a numeric sort, you can chain distance logic with mapping functions. Assume you have a database of 200 potential client addresses in A2:A201. You want to isolate the five closest addresses to your current location, which is entered in cell C1. First, extract those specific entries using =CLOSEST_TO(A2:A201, C1, 5). If this output populates cells D2:D6, you can pass that exact range into the map generator: =INSTAMAP(D2:D6). The =INSTAMAP() function returns a live, hosted shareable map URL. If you alter the target address in C1, the CLOSEST_TO list updates, which automatically forces the INSTAMAP URL to reflect the new locations.
Limits and Honest Alternatives
The =DISTANCE formula uses the Haversine method to calculate the straight-line geographical gap between two sets of coordinates. It draws a literal line across the earth's surface. It does not route along roads, nor does it account for one-way streets, motorway access, bridges, tolls, or current traffic conditions.
If you are calculating a route across a city centre, the straight-line distance to a stop located across a river might read as 1.4 miles, when the actual driving distance over the nearest bridge is 4.2 miles. If you require drive times, road distances, or traffic estimates, do not use =DISTANCE. Instead, use =TRAVEL_TIME(A2, B1), which queries the road network to return accurate transit durations in minutes. You can also use =ROUTE_LINK() to open the official Google Maps interface, which allows a maximum of 11 stops.
Daily lookup constraints apply. The InstaMaps add-on is free, but Google enforces quotas on its mapping services. The standard free tier allows 100 lookups per day. Completing the free email unlock raises this limit to 1,000 lookups per day. A lookup occurs each time a formula requests new data from the servers. If you process a 500-row sheet on the standard 100-lookup tier, the formula will return a quota error message at row 101. To prevent quota exhaustion, standardise your address formats using =CLEAN_ADDRESS before calculating distances, and avoid altering target cells unnecessarily.
Who This Formula Is For
This function is designed for users who need fast, numeric outputs to sort and filter data, rather than visual map routing.
Local delivery dispatchers: Managers assigning 47 daily stops to 5 separate crews use the output to balance workloads. By calculating the straight-line distance from the depot to each stop, dispatchers can group stops into numeric clusters without waiting for slower, turn-by-turn routing algorithms to process.
Real estate analysts: Professionals evaluating a 200-home property farm need to measure proximity to local amenities. Running =DISTANCE populates a numeric column, allowing analysts to filter out properties situated further than 2 miles from a specific school or transit station.
Sales territory managers: Field representatives planning daily client visits need to prioritise leads located near their scheduled appointments. This formula provides the raw numbers required to sort a list of prospects by physical proximity, enabling efficient routing based on spatial awareness.
Facilities managers: Maintenance coordinators overseeing regional infrastructure rely on this output to identify the closest available technician to a reported fault based purely on a geographic radius.
Map your Salesforce accounts in under 5 minutes — no admin setup.
Common Questions
Install the InstaMaps add-on and use the =DISTANCE() function, pointing it at the cells containing your start and end points. For example, if your starting addresses are in column A and destinations in column B, type =DISTANCE(A2, B2) in column C. You can then drag the formula down to calculate distances for hundreds of rows simultaneously. The output is a raw number, making it easy to sum, average, or sort.
No, the =DISTANCE() formula calculates the straight-line geographical distance between two points. If you need driving distances or estimated travel times that account for actual road routes and traffic conditions, you should use the =TRAVEL_TIME() function instead. Straight-line distances are significantly faster to compute and are highly effective for radius filtering or rough proximity sorting where exact drive times are not required.
A #NAME? error indicates that Google Sheets does not recognise the formula, which usually means the InstaMaps add-on is not installed or enabled in your current document. You must install the add-on from the Google Workspace Marketplace to grant the script the necessary permissions. Check out our [google-sheets-geocode-formula](https://get-instamaps.com/google-sheets-geocode-formula) guide for troubleshooting common formula errors and ensuring your custom functions execute properly.
Because =DISTANCE() outputs a standard numerical value rather than text, you can apply native Google Sheets sorting and filtering to the results column. Alternatively, you can use the dedicated =SORT_BY_DISTANCE() formula, which automatically reorders an entire array of addresses based on their distance from a single reference point. This creates a highly efficient workflow for ranking a list of destinations by proximity without manual sorting.
The base free tier provides 100 lookups per day, which resets every 24 hours. If you need to process larger datasets, you can expand this limit to 1,000 lookups per day by registering with a free email address directly through the add-on interface. This usage limit applies to geocoding and distance calculations across your entire account, ensuring stable service across all users.
Yes, =DISTANCE() can read postcodes, partial addresses, or full street addresses, as long as the locations can be resolved on the map. If you have a large list of postcodes, you can use =DISTANCE(A2, B2) where A2 and B2 contain postcode strings. If your location data is messy, use the =CLEAN_ADDRESS() formula first to standardise the formatting before calculating the distances.
Stop pasting addresses into Google Maps one by one. Install InstaMaps to generate straight-line distances, sort addresses by proximity, and build live workflow maps.
Install InstaMaps free