A distance matrix is a grid that shows the travel distance or drive time from every starting location to every ending location. It answers questions like “which warehouse is closest to each customer?” or “which office gives each employee the shortest commute?” in one table.
Building one usually means either setting up a Google Maps API key and writing a script, or typing a formula for every single pair of locations. This tutorial shows a faster way: the Route Matrix add-on fills the whole grid in Google Sheets with one click, with no API key and no code.
What You’ll Build
By the end, you’ll have a new tab with your starting locations down the left side, your ending locations across the top, and the driving distance or time in every cell. Optional totals show which location has the lowest overall distance at a glance.
Step 1: Enter Your Locations
Put your starting locations in one column and your ending locations in another. Each list can be any length, and the two lists don’t need to be the same size. For this example, we’ll compare three customer towns against three possible depot locations:
| A | B | |
|---|---|---|
| 1 | Customers | Depots |
| 2 | Fort Collins, CO | Denver, CO |
| 3 | Aurora, CO | Boulder, CO |
| 4 | Pueblo, CO | Colorado Springs, CO |
Locations can be full street addresses, city names, ZIP codes, latitude and longitude coordinates, Plus Codes, or place chips. If Google Maps recognizes it, Route Matrix can use it. Full street addresses give the most precise results.
Step 2: Open Route Matrix
Install Route Matrix from the Google Workspace Marketplace if you haven’t already. It opens from the add-ons side panel on the right of your sheet; click the Route Matrix icon there.
If you’d like to try it before entering your own data, click Try a Sample on the welcome card. Route Matrix adds a small tab of example addresses and fills in the settings for you, so you can click RUN right away.
Step 3: Choose the Starting and Ending Ranges
- Sheet with Address Data: the tab that holds your lists
- Range of Starting Address(es): A2:A4 in this example. You can type the range or select the cells and click Use Active Selection.
- Range of Ending Address(es): B2:B4
Starting locations become the rows of the matrix and ending locations become the columns. If your addresses are long, tick Use custom labels for locations and point Route Matrix at a column of short names; the full addresses then appear as notes on the label cells.
Step 4: Pick Your Options
- Unit of Distance: Miles or Kilometers
- Output: Distance or Time
- Route Type: Driving, Walking, Bicycling, or Transit
- Departure Time (optional): a future day and time. Driving times then include predicted traffic, and transit uses that time’s schedule.
- Formatting: Formatted adds colored headers and a green-to-red scale on the totals
- Totals: Column sums, Row sums, or both
- Show quickest routes: lists the closest ending locations for each starting location next to the grid
Step 5: Click RUN
Click RUN. Route Matrix adds a new tab named Route Matrix with a short summary of the run at the top, followed by the grid. Each cell holds the distance or drive time from the location on its row to the location in its column.
With Formatted output and Column sums turned on, the column totals are colored from green (lowest total) to red (highest). In the example below, which compares 3 customer addresses against 3 sales reps’ home bases, the green total is the home base with the least total driving.
The matrix is ordinary spreadsheet data, so you can sort it, chart it, or reference it in formulas like any other range. To refresh it after your addresses change, click RUN again; each run creates a new tab so earlier results stay put.
How Much It Costs
Runs of up to 9 routes are free. A route is one starting location paired with one ending location, so the 3 × 3 example above is 9 routes and costs nothing. Larger runs use paid routes, at $15 for 1,000 or $50 for 5,000, which you can also buy inside the add-on.
- Before a paid run, Route Matrix shows how many paid routes it will use and waits for you to confirm. You’re charged only if the run finishes.
- A driving route with a departure time includes traffic and counts as two routes.
- Paid runs handle 5,000 or more routes at once; a 5,000-route matrix finishes in under 20 seconds.
- If a run is ever too large, Route Matrix stops before charging and tells you what size to split it into.
For example, 50 customers against 5 possible sites is 250 routes, about $3.75 worth from a pack of 1,000.
Other Ways to Build a Distance Matrix
The Google Maps API
Google Maps Platform offers APIs that return distances between many origins and destinations. To use it from Sheets, you need a Google Cloud project with billing turned on, an API key, and an Apps Script or other code to send the request and write the results back to your sheet. It’s a good fit for developers building it into an app, but it’s a lot of setup for a one-off analysis.
One Formula per Pair
The TRAVELDISTANCE and TRAVELTIME functions from Travel Formulas calculate one route per cell. Put starting locations down column A and ending locations across row 1, then enter =TRAVELDISTANCE($A2, B$1) in B2 and fill it across and down. This works well when you want the matrix to update as a live formula, but every cell is a separate lookup.
Which One to Use
Use Route Matrix when you want the whole grid at once without formulas, keys, or code. Use the formulas when you want results that recalculate inside a larger spreadsheet model.
What You Can Do With a Distance Matrix
- Choose a warehouse or office location by comparing total distance to every customer
- Assign sales territories by drive time from each rep to each account
- Compare employee commutes to one or more offices
- Find the nearest store, technician, or service center for each customer address
- Pick a meeting spot that keeps total travel time low for a distributed team
- Compare commutes before an office move to see which employees gain and which lose
For bigger decisions, such as assigning every customer to one depot while keeping each depot’s workload within a limit, SolveSheet can use the Route Matrix grid as the cost table for an optimization model.
Frequently Asked Questions
Do I need a Google Maps API key?
No. Route Matrix handles the Google Maps lookups for you. There’s nothing to set up in Google Cloud and no key to paste in.
How many locations can I include?
Free runs cover up to 9 routes. With paid routes, a single run can handle 5,000 or more routes, such as 100 starting locations and 50 ending locations.
Does Route Matrix use real driving routes?
Yes. Results come from Google Maps routing, so distances follow actual roads rather than straight lines. Without a departure time, drive times don’t include traffic; set one to include predicted traffic.
Can I get walking, cycling, or transit distances?
Yes. Choose Walking, Bicycling, or Transit as the Route Type. For transit, set a departure time to use that time’s schedule.
Can I use kilometers instead of miles?
Yes. Set Unit of Distance to Kilometers before you click RUN.
Get Started
A distance matrix turns a list of addresses into an answer: which location is closest, which site is cheapest to serve, or which assignment saves the most driving. Install Route Matrix and build your first one in a couple of minutes.