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

  1. Open the sidebar with Extensions > Geocode & Map Addresses > Open geocoder and click the Distance tab.
  2. 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.
  3. Pick the destination columns under To (coordinates in each row), usually the Latitude and Longitude columns the geocoder added.
  4. Under Measure, choose Driving distance and time.
  5. Pick Miles or Kilometers under Units.
  6. 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:

CustomerLatitudeLongitudeDistance (mi)Drive time (min)
Corner Marketfilled by geocodingfilled by geocodingwritten by the add-onwritten 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 lineDriving
LookupsNone2 per row
Drive timeNoYes, in minutes
CoverageAnywhere you have coordinatesUnited 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).


More Geocode & Map Addresses guides


More Google guides