Four ways to turn a column of addresses into road miles — and what each one actually costs you.
Excel has no built-in function that returns driving distance. That single fact is why route mileage is such a persistent chore: the spreadsheet can total the miles, average them, split them by month and price them out — but it cannot fetch them. The miles have to come from somewhere else first.
There are four practical ways to close that gap. They are not equally good, and the right one depends almost entirely on how many rows you have.
Open a maps site, type the two addresses, read the distance, type it into the cell. No setup, no cost, and for a handful of trips it is genuinely the correct answer — setting anything else up would take longer than the lookups.
It stops working around thirty rows. The cost is linear in trips and entirely in your time, and every value is typed by hand, which means every value is a chance to transpose a digit. Nothing revalidates when an address is corrected later.
Search for this problem and you will quickly find a trigonometry formula that computes distance from latitude and longitude in pure Excel, with no internet connection:
RouteMetrics
=3959*ACOS(COS(RADIANS(90-Lat1))*COS(RADIANS(90-Lat2))+SIN(RADIANS(90-Lat1))*SIN(RADIANS(90-Lat2))*COS(RADIANS(Lon1-Lon2)))
Great-circle distance in miles (3959 is the earth's radius in miles).
This works exactly as advertised, and it is often presented as the answer to "calculate driving distance in Excel." It is not. It measures the straight line across the earth's surface — the path a bird takes, not a truck. Real routes bend around lakes, rivers, mountains, restricted roads and one-way streets, so road mileage is always higher — commonly 10–25% more, and considerably more wherever geography forces a detour.
Columbus to Chicago makes the gap concrete: the formula above returns 276 miles, while the drive is about 355 miles — roughly 80 miles, and well over an hour, that simply are not in the straight-line number.
Two other limits matter in practice: it needs latitude and longitude, not addresses, so you still have to geocode every stop somehow. And a figure that is systematically short is not something you can put on a reimbursement claim or a tax record. Straight-line distance is the right tool for "roughly how far apart are these?" and the wrong tool for anything that has to be defensible.
The traditional power-user answer: write a macro that calls a mapping provider and returns the driving distance. It gives real road miles and it automates the column, which puts it well ahead of the first two options.
What it asks in return is a cloud account with billing enabled, an API key to create and protect, and a macro-enabled workbook. That last one is the quiet dealbreaker in a lot of organisations: macro-enabled files are frequently blocked by policy, they trigger security prompts when shared, and they do not run in Excel for the web at all. A workbook that only calculates on one machine tends not to survive contact with a team.
The fourth option is to give Excel the function it is missing. RouteMetrics adds routing formulas to the spreadsheet, so road miles become a calculation like any other:
=ROUTE.DISTANCE("Columbus, OH", "Chicago, IL")
→ returns the driving distance in miles.
Because it is an ordinary formula it takes cell references, fills down a column, and recalculates when an address changes. There is no API key to manage, no macros, and no cloud account — and it runs in Excel for Windows, Mac and the web, so the workbook still calculates after you send it to someone else.
"Route mileage" usually means a whole route rather than a single leg. The pattern is to put each stop on its own row and treat every consecutive pair as one leg — a route with five stops has four legs.
With stops in column A, put the mileage for each leg in column B:
=ROUTE.DISTANCE(A2, A3)
Fill down, then =SUM(B2:B5) for the total route mileage.
Each row now reads as one leg of the route, the total updates when a stop changes, and
inserting a stop mid-route re-costs everything below it automatically. Drive time works
the same way with =ROUTE.TIME, which is the other half of most route
planning.
Route mileage is rarely the end of the job. Two common next steps, both of which have a free template here:
Get RouteMetrics on Microsoft AppSource See pricing
100 free searches, no time limit. Works in Excel for Windows, Mac, and the web.
List each leg on its own row with a start and end address, then fill a mileage column.
Excel has no built-in driving-distance function, so the miles come from a manual maps
lookup, a VBA macro calling a mapping API, or a routing add-in that provides a formula
like =ROUTE.DISTANCE(A2, B2). Sum the column for the route total.
No. Excel's trigonometry functions can compute a straight-line great-circle distance from latitude and longitude, but that is not driving distance. Road mileage runs commonly 10–25% higher, and more where geography forces a detour.
Straight-line distance measures directly across the earth's surface and ignores roads. Route miles follow the road network, including every detour around water, terrain and one-way streets. Straight-line is always shorter, which is why it should not be used for reimbursement or tax records.
Put each stop on its own row and treat consecutive pairs as legs — five stops make four legs. Calculate each leg, then sum the column.
Related: Distances in Excel without an API key · Free mileage log template · Mileage reimbursement calculator · IFTA mileage worksheet · All Excel templates & guides