How to Calculate Distance Between Two Addresses in Excel
Excel has no built-in function for this. There is no
=DISTANCE(A2, B2). Every method below works, but each one
asks something of you — coordinates you may not have, an API key,
a macro your IT department may block, or Windows.
Which one is right depends on two questions: do you need driving distance or straight-line distance, and are you doing five rows or five hundred? Answer those first and the choice makes itself.
The short answer
- A handful of trips, driving distance: look each one up in Google Maps and type it in. Genuinely faster than any formula.
- You already have latitude and longitude, straight line is fine: the Haversine formula, below. No internet required.
- Dozens of rows, driving distance, Excel on Windows:
WEBSERVICEwith a mapping API. - Hundreds or thousands of rows: none of the above scales comfortably. Skip to the last section.
First: straight-line or driving?
This is the step most tutorials skip, and it is the one that produces wrong numbers. The Haversine formula — the one you will find in most search results — returns great-circle distance: the direct line between two points, as though you could drive through buildings and across water.
Real driving distance is almost always longer, because roads bend around things. How much longer depends entirely on geography: a straight interstate run is close to the direct line, while a coastal or mountain route can be far off it.
If the number is going into a mileage reimbursement, an invoice, or a delivery plan, you need driving distance. A straight-line figure will understate every single trip, and it is not a number you would want to defend.
Method 1: The Haversine formula
Works offline, costs nothing, and needs no API key — but it needs latitude and longitude, not addresses. If you only have street addresses, you must geocode them first, which puts you back in API territory.
With latitude in columns A and C, and longitude in B and D:
=2*3959*ASIN(SQRT(SIN(RADIANS((C2-A2)/2))^2+COS(RADIANS(A2))*COS(RADIANS(C2))*SIN(RADIANS((D2-B2)/2))^2))
3959 is the Earth's radius in miles — swap it for
6371 to get kilometres. Fill the formula down and every row
calculates instantly, with no rate limits and no network calls.
Remember what it returns: a straight line, not a route.
Method 2: WEBSERVICE and FILTERXML
This calls a mapping API straight from a cell, with no code. You sign up
for a maps API key, build the request URL from your address cells with
WEBSERVICE, then pull the distance out of the XML response
with FILTERXML.
The catch is a big one: both functions are Windows-only. Microsoft's own documentation notes they rely on Windows operating system features, so they return nothing in Excel for Mac or Excel for the web — even though they still appear in the function list, which makes the failure confusing rather than obvious.
They also fire one API call per row, every time the sheet recalculates. That is fine for twenty rows and a problem for two hundred.
Method 3: A VBA custom function
Writing a small VBA function gives you the most control: proper error
handling, caching so you do not re-request the same pair, and a tidy
=DRIVINGDISTANCE(A2, B2) to use in the sheet. It works on
Mac as well as Windows.
The cost is that your file becomes a macro-enabled workbook. Plenty of organisations block those outright, and colleagues who need to open the file may not be able to run it. If you are the only person using the spreadsheet, this is a good option. If you are going to email it around, it usually is not.
Which method fits
| Method | Gives driving distance | Needs | Comfortable up to |
|---|---|---|---|
| Look it up by hand | Yes | Patience | ~10 rows |
| Haversine formula | No — straight line | Latitude & longitude | Any number |
| WEBSERVICE + FILTERXML | Yes | API key, Excel on Windows | ~50 rows |
| VBA function | Yes | API key, macros enabled | ~200 rows |
When the list is too long for any of them
Every method above calculates one row at a time. That is the constraint that eventually bites: a thousand rows means a thousand sequential API calls, each one subject to rate limits, any of which can fail and leave you with gaps you have to find and re-run by hand. You end up maintaining a small piece of software instead of filling in a spreadsheet.
That is the point at which a batch tool is simply the more sensible
choice. TripTally Plus takes the whole spreadsheet at
once — upload an .xlsx or .csv, connect an
Excel Online or OneDrive workbook, or paste two columns straight in
— and returns driving distance and drive time for every row from
Google Maps data. No API key, no macros, and it works the same on Mac,
Windows and the web.
Every account starts with 100 free calculations, and you can see exactly what comes back before creating one.
No credit card required
Frequently asked questions
Does Excel have a built-in distance function?
No. There is no native function that turns two addresses into a distance. Every approach needs either coordinates or a mapping API.
Why does my Haversine result not match Google Maps?
Because they measure different things. Haversine gives the direct line between two points; Google Maps gives the driving route. The route is almost always longer.
Why does WEBSERVICE return nothing on my Mac?
It is not available on Excel for Mac or Excel for the web. The function appears in the list, but it depends on Windows features and will not return a result.
Can I do the same thing in Google Sheets?
Yes. Sheets has no built-in distance function either, but Apps Script can add a custom function, and TripTally Plus reads Google Sheets directly as well as Excel.
Also useful for: employee mileage reimbursement · logistics & delivery distances · real estate travel times · all features