How to build a distance matrix in Google Sheets using =DISTANCE

To calculate a distance matrix in Google Sheets, use the InstaMaps add-on to place origins in column A and destinations in row 1. Apply the =DISTANCE($A2, B$1) formula across the grid. This generates pairwise distances between all coordinates or addresses without requiring complex scripts.

This setup is for logistics coordinators and delivery planners managing a 200-home farm with 5 crews. Instead of manually checking maps, you generate a full grid of straight-line distances. You can then rapidly sort stops, build route links, and share a hosted map URL directly from your spreadsheet data.

Constructing a pairwise grid with =DISTANCE and absolute refs

Constructing a pairwise distance matrix in Google Sheets requires a strict application of absolute cell references. When you need to calculate the straight-line distances between multiple origins and destinations, you cannot rely on relative referencing. If you place your 15 origin coordinates down column B (cells B2:B16) and your 12 destination coordinates across row 1 (cells C1:N1), the formula must lock both axes to drag correctly.

To lock the origin column, prepend a dollar sign to the column letter: `$B2`. This ensures that when you drag the formula horizontally to the next destination column, the calculation continues to read from column B. To lock the destination row, prepend a dollar sign to the row number: `C$1`. This ensures that when you drag the formula down to the next origin row, the calculation continues to read from row 1.

Combining these absolute references gives the exact syntax needed for the top-left cell of your matrix: `=DISTANCE($B2, C$1)`. Because `=DISTANCE()` calculates the geographical distance between two points, the coordinates must be valid. If your origins in column B were generated using `=GEOCODE(A2:A16)`-where column A contains text addresses-and your destinations in row 1 were generated from a separate range, this reference structure prevents broken calculations.

Once you input `=DISTANCE($B2, C$1)` into cell C2, you select C2 and drag the fill handle across to N2. Then, with the entire C2:N2 range highlighted, you drag the fill handle down to row 16. The resulting C2:N16 grid calculates 180 individual pairwise distances. Cell N16 will correctly display `=DISTANCE($B16, N$1)` without manual adjustment.

This absolute referencing method prevents `#REF!` errors when expanding your grid. However, note that `=DISTANCE()` currently calculates straight-line distance, not road travel time. An honest caveat: a true travel-time matrix is in active development and coming soon. Until then, this straight-line grid provides the necessary baseline data for logistical planning, proximity analysis, and radius filtering in standard spreadsheet workflows.

Step-by-step: Setting up your Google Sheets matrix

Setting up a distance matrix requires converting text addresses into coordinates before calculating the distances between them. Typing these formulas manually often introduces broken arrays or syntax errors. The InstaMaps sidebar allows you to insert the exact functions needed to map your data without typing the brackets and commas yourself.

1. Open the sidebar: Go to Extensions > InstaMaps > Enable formulas to open the tool panel on the right side of your sheet.

2. Prepare the data: In Sheet1, list 30 origin addresses down column A (A2:A31).

3. Geocode origins: Select cell B2. Use the sidebar to insert `=GEOCODE()` and apply it to your range. Cell B2 will populate with `=GEOCODE(A2:A31)`, generating coordinates down column B.

4. Geocode destinations: In cell D1, list 10 destination addresses across the row (D1:M1). Select D2, and use the sidebar to insert `=GEOCODE(D1:M1)`. The coordinates will expand across row 2.

5. Set the grid references: In cell D3, insert the distance formula. The exact syntax required to lock the references is `=DISTANCE($B3, D$2)`.

6. Expand the matrix: Press Enter on D3. Select D3 and drag the fill handle to the right until M3. With D3:M3 still highlighted, drag the fill handle down to row 31.

7. Automate the workflow: If you have a complex chain of formulas, click the Build-the-workflow button in the sidebar to write the entire sequence automatically.

8. Check lookup limits: The free tier handles 100 lookups per day (1,000/day with a free email unlock). A 30x10 matrix consumes 40 of your daily geocoding limits on the first run.

Worked example: Allocating 47 stops across 5 service crews

Consider a regional operations manager who needs to assign 47 client sites to 5 distinct service crews. Each crew departs from a different depot. Building a distance matrix identifies which depot is closest to each client site, ensuring travel times remain low.

In column A, list the 47 client site addresses (A2:A48). In column G, list the 5 depot addresses (G2:G6). To calculate the pairwise grid, first geocode both sets. In B2, run `=GEOCODE(A2:A48)`. In H2, run `=GEOCODE(G2:G6)`.

Next, build the matrix. If you place the 5 depot coordinates across row 1 (I1:M1), you can map the distances from the 47 client sites down the rows. In cell I2, input `=DISTANCE($B2, I$1)`. Dragging this formula across to M2, then down to M48, creates a 5x47 grid containing 235 specific distance calculations in miles or kilometres.

While the matrix provides the raw numbers, finding the lowest distance for each row manually is inefficient. Instead of scanning the grid, use the `=CLOSEST_TO()` function. In cell C2, type `=CLOSEST_TO(B2, H2:H6)`. This formula checks the geocoded location of the client site in B2 against the list of 5 depot locations in H2:H6. It then returns the exact name or address of the nearest depot.

Because the matrix contains 47 rows of client sites, you drag the `=CLOSEST_TO()` formula down to C48. Within seconds, the manager sees that client site A12 routes to Depot 3, and client site A40 routes to Depot 1. The matrix remains untouched in the background for audit purposes, while the `=CLOSEST_TO()` column provides the final operational assignments for the 5 crews.

For managers needing a shareable view, entering `=INSTAMAP()` returns a live hosted shareable map URL that plots these 47 sites and 5 depots. When a client cancels or is added to column A, the geocoding updates, the distance matrix recalculates, the closest depot reassigns, and the hosted map reflects the new pins.

Ranking destinations from a single origin with =SORT_BY_DISTANCE

Constructing a full pairwise matrix is unnecessary when your logistical needs only require ranking destinations from a single, fixed starting point. If a delivery driver starts at a central warehouse and needs to visit 20 specific drop-off locations, building a 1x20 grid wastes spreadsheet space. The `=SORT_BY_DISTANCE()` function replaces the matrix entirely.

Assume the warehouse address is geocoded in cell B2. The 20 destination addresses are listed in A3:A22, and geocoded in B3:B22. To view the locations ranked by proximity to the warehouse, select cell C3 and type `=SORT_BY_DISTANCE(B2, A3:B22)`. The formula takes the single origin point as the first argument, and the two-column array containing the destination addresses and their coordinates as the second.

Instead of requiring you to drag formulas across a grid, `=SORT_BY_DISTANCE()` outputs a dynamic array directly below the formula cell. The output automatically expands into neighbouring columns, displaying three specific columns of data: the destination address, the calculated distance, and the rank order (1 through 20).

The nearest location appears at the top of the list in cell C3, with its exact distance in cell D3. The furthest location appears at the bottom in row 22. If a new address is appended to cell A23 and the range in the formula is updated to `A3:B23`, the sorted list automatically reshuffles to integrate the new destination in its correct rank. This provides a clean, readable itinerary without generating an underlying grid of calculation cells.

Limits and honest alternatives for large matrices

Building a 100x100 pairwise grid in a standard Google Sheet requires 10,000 individual cell calculations. If you write =DISTANCE($A2, B$1) and drag it across a 100x100 matrix, the sheet will attempt to recalculate all 10,000 cells simultaneously. This will severely degrade sheet performance, often freezing the tab or timing out entirely before the results render.

Beyond spreadsheet performance, you face hard API quotas. The InstaMaps free tier provides 100 lookups per day, which increases to 1,000 lookups per day with a free email unlock. A single 100x100 matrix will exhaust your daily allocation in seconds. When you exceed the quota, the formulas will return exact errors instead of mileage.

We are honest about the current limitations: standard Google Sheets is not designed for massive, two-dimensional distance grids. A dedicated, optimised =DISTANCE_MATRIX() custom function and a true travel-time grid are actively being developed. This upcoming function will process the entire array in one efficient request rather than forcing the sheet to calculate individual cell paths.

Until that dedicated matrix function is live, avoid building grids larger than 15x15 (225 calculations). Instead of a full matrix, use =SORT_BY_DISTANCE(A2, B2:B50) to calculate and rank the nearest destinations from a single origin. If you need to process a 200-home delivery grid today, you should rely on dedicated routing software rather than standard spreadsheet formulas.

Who this workflow is for (and who should wait)

If you fit the ideal user profile, you do not need to type formulas manually. Open the sidebar via Extensions > InstaMaps > Enable formulas. Clicking the Build-the-workflow button inserts the entire formula chain into your sheet automatically, ensuring absolute references like $A2 are formatted correctly to prevent broken matrix calculations.

1. Small logistics managers: Planning a 5-crew deployment across 47 stops. You can calculate the driving distance from your depot to specific neighbourhoods without exceeding your 1,000 daily lookups.

2. Regional sales route planners: Needing a quick one-to-many distance sort from a single hotel to 15 client offices. =SORT_BY_DISTANCE() immediately ranks your closest prospects.

3. High-volume fleet dispatchers: Handling daily 200-stop delivery grids will find the daily lookup limits too restrictive. A 100-vehicle matrix burns through the quota instantly.

4. Users needing true travel-time optimisation: If you require complex optimisation with live, traffic-adjusted drive times, you should wait for the upcoming =DISTANCE_MATRIX() function or purchase dedicated routing software.

Visualising your distance data with =INSTAMAP

Calculating pairwise distances is only useful if you can visualise the results spatially. Once you have used =DISTANCE() and =SORT_BY_DISTANCE() to filter your list down to the 10 closest destinations, you can map them.

Select your cleaned address data, for example =INSTAMAP(C2:C15). The =INSTAMAP() formula returns a live hosted shareable map URL directly in the cell.

Because the URL is dynamic, the hosted map automatically updates when your underlying sheet data changes. If you delete a row or add a new destination in A12, the map reflects the change immediately without requiring you to regenerate the link. You can also pair this map with =ROUTE_LINK(C2:C12), which uses Google Maps' official URL scheme (max 11 stops) to provide driving directions for the exact route you just calculated.

Common Questions