How to calculate driving distance in Google Sheets
By Jake Marsh · Updated
To calculate driving distance in Google Sheets for a whole list, geocode the addresses with the Geocode & Map Addresses add-on, then open its Distance tab, choose a starting point, pick the Latitude and Longitude columns as the destination, and select Driving distance and time. Each row gets a distance in miles or kilometers and a drive time in minutes. Sheets has no built-in formula for driving distance.
Before you start
Driving distance is measured to coordinates, so the destination rows need Latitude and Longitude columns. If your sheet only has addresses, geocode them first: Extensions > Geocode & Map Addresses > Open geocoder, pick the address columns, Check rows, then Geocode N rows.
Steps
- Open the sidebar with Extensions > Geocode & Map Addresses > Open geocoder and click the Distance tab.
- Choose where each trip starts under From:
- One address for every row, such as your warehouse: type it, for example
1600 Main St, Dallas, TX 75201. It's geocoded once and remembered for the spreadsheet. - Coordinates in each row, when every row has its own start, such as a technician's base. Pick that latitude and longitude column.
- One address for every row, such as your warehouse: type it, for example
- Pick the destination columns under To (coordinates in each row), usually the Latitude and Longitude columns the geocoder added.
- Under Measure, choose Driving distance and time.
- Pick Miles or Kilometers under Units.
- Click Check rows to see how many rows will be measured and the most lookups it can use, then Run N rows.
The add-on adds Distance (mi) or Distance (km), Drive time (min) and Distance status columns to the right of your data.
Example
A bakery delivering from one shop to 400 wholesale customers:
| Customer | Latitude | Longitude | Distance (mi) | Drive time (min) |
|---|---|---|---|---|
| Corner Market | filled by geocoding | filled by geocoding | written by the add-on | written by the add-on |
Sort by Drive time (min) to plan routes, or filter for rows under 30 minutes to see who sits inside a delivery window.
Driving or straight line
The same tab offers Straight line (no lookups used), the distance as the crow flies. It's right for radius checks ("within 25 miles"), and it costs nothing. Driving distance follows real roads and adds a drive time, and it uses 2 lookups per row. For a quick check of a few coordinate pairs outside a spreadsheet, the free distance between coordinates calculator gives straight-line distances in your browser.
| Straight line | Driving | |
|---|---|---|
| Lookups | None | 2 per row |
| Drive time | No | Yes, in minutes |
| Coverage | Anywhere you have coordinates | United States, Canada and Mexico |
What it costs
The free trial covers 250 lookups or 14 days, whichever comes first, with no card. Then it's $9.99 a month or $79 a year per Google account, which includes 5,000 lookups every month, so a plan covers driving distances for about 2,500 rows a month. Lookup packs add 5,000 for $10 and never expire. Install it from the Geocode & Map Addresses page.
Geocode & Map Addresses by Great Work
Geocode, reverse geocode and map addresses in Google Sheets. No API key needed. $9.99 a month or $79 a year per Google account.
Questions
Is there a Google Sheets formula for driving distance?
No. Sheets has no built-in driving distance function. The add-on calculates it for every row at once and writes the result as plain numbers.
Can I measure from a different starting point on each row?
Yes. Choose Coordinates in each row under From and pick the start latitude and longitude columns.
Why does driving distance cost 2 lookups per row?
A route lookup costs more than a single address lookup. The add-on shows the most lookups a job can use before it runs.
Can I get kilometers instead of miles?
Yes. Pick Kilometers under Units before you run, and the column is named Distance (km).