To calculate the distance between two points in Excel, check what your coordinates measure. X and Y positions on a flat grid measure distance from an origin. Latitude and longitude measure angles, so GPS data needs a different formula.
I’ll show you how to estimate GPS distance with the Haversine formula and measure across a flat grid with SQRT. For GPS lists you update regularly, I’ll also show you how to run the calculation in Power Query.
| If You Have | Use | Result |
|---|---|---|
| Latitude and longitude in decimal degrees | Haversine formula | Approximate distance over Earth’s surface |
| X and Y positions on a flat grid | SQRT formula | Straight-line distance in the same units as your coordinates |
| GPS data you import regularly | Power Query | A refreshable table of surface distances |
Method #1: Applying the Haversine Formula
I use the Haversine formula when my inputs are latitude and longitude in decimal degrees. It estimates the shortest distance over Earth’s surface by treating Earth as a sphere.
On the Harbor Distances sheet, each row has the start and end coordinates for a simulated harbor leg. The starting latitude and longitude are in columns B and C, and the ending latitude and longitude are in D and E.
In Excel 2021, Excel 2024, or Microsoft 365, enter this formula in F2. It spills the eight distances down to F9.
=LET(hav,SIN(RADIANS(D2:D9-B2:B9)/2)^2+COS(RADIANS(B2:B9))*COS(RADIANS(D2:D9))*SIN(RADIANS(E2:E9-C2:C9)/2)^2,2*6371*ASIN(SQRT(IF(hav>1,1,hav))))
The first result is about 2.167 km from Ferry Pier to Red Buoy. South Slip to Patrol Float comes to about 56 m, and the East Pier recheck gives 0 km because both positions are the same.
How the formula works
RADIANS converts the latitude values and coordinate differences from degrees to radians. The SIN and COS terms use those angles to find a value that reflects how far apart the two points are on the sphere.
LET names that value hav. Floating-point rounding can push it slightly above 1, so IF caps it at 1 for each row.
SQRT and ASIN help turn it back into an angle. Doubling that angle and multiplying by Earth’s approximate radius of 6,371 km gives the distance in kilometers.
If you’re using Excel 2019 or earlier
Enter this version in G2 and fill it down to G9. It uses the same Haversine calculation for one row at a time. MIN caps that row’s value at 1.
=2*6371*ASIN(SQRT(MIN(1,SIN(RADIANS(D2-B2)/2)^2+COS(RADIANS(B2))*COS(RADIANS(D2))*SIN(RADIANS(E2-C2)/2)^2)))
Method #2: Using the SQRT Function
For positions on a flat grid, you can use the X and Y differences as the legs of a right triangle. The distance between the points is the length of its diagonal.
The Stage Distances sheet lists eight checks between marked positions on a stage. Columns B and C hold each starting X and Y position, and columns D and E hold the ending positions. All four columns use meters.
Enter this formula in F2. In Excel 2021, Excel 2024, or Microsoft 365, it spills down to F9.
=SQRT((D2:D9-B2:B9)^2+(E2:E9-C2:C9)^2)
The first row goes from (0, 0) to (3, 4). The two differences are 3 m and 4 m. The formula squares them, adds the squares to get 25, and takes the square root. The result is 5 m.
If you’re using Excel 2019 or earlier
Enter the single-row version in G2 and fill it down to G9. It returns the same results as the spilling formula.
=SQRT((D2-B2)^2+(E2-C2)^2)
Negative coordinates work here, too. From Left Wing to Drum Riser, X goes from -8 to -3 and Y goes from 1 to 5. The distances along the axes are 5 m and 4 m, so the two points are about 6.40 m apart.
Method #3: Adding a Power Query Custom Column
If you get a new list of GPS coordinates every week, you can put the Haversine calculation in a Power Query custom column and refresh the results after you update the source.
I’m using the same eight simulated harbor legs on the Harbor Import sheet. The example workbook already has an Excel table called HarborLegs. Start at step 2 if you’re following along with that table.
Here’s how to set it up in Excel for Windows.
- Click inside your coordinate range and press Ctrl+T. Confirm the range, check My table has headers, and click OK.
- Click inside the table and choose Data > From Table/Range to open the Power Query Editor.
Check that Power Query reads all four coordinate columns as decimal numbers. The column names in this example are Start Lat, Start Lon, End Lat, and End Lon. The formula uses these names as written.
- In the Power Query Editor, choose Add Column > Custom Column.
- Enter Distance (km) as the new column name. Paste the expression below into the formula box after the equals sign, then click OK.
let
rad = Number.PI / 180,
startLat = [Start Lat] * rad,
endLat = [End Lat] * rad,
dLat = endLat - startLat,
dLon = ([End Lon] - [Start Lon]) * rad,
hav = Number.Power(Number.Sin(dLat / 2),2) +
Number.Cos(startLat) * Number.Cos(endLat) * Number.Power(Number.Sin(dLon / 2),2)
in
2 * 6371 * Number.Asin(Number.Sqrt(if hav > 1 then 1 else hav))
The expression converts the coordinates to radians using π divided by 180. It calculates the Haversine distance for the current row, so each harbor leg gets its own result.
- Choose Home > Close & Load to load the results to a new worksheet.
The loaded table in the workbook is on Query Distances. Its first row shows the same distance as the worksheet formula, about 2.167 km.
When you add or replace rows in the source table, choose Data > Refresh All in Excel to update the loaded results.
Additional Notes About Calculating Distance Between Coordinates in Excel
Check the coordinates before you trust the result.
- Keep latitude and longitude in the right columns. Latitude runs from -90 to 90, and longitude runs from -180 to 180. West longitudes and south latitudes use negative values.
- Check for incomplete pairs. Excel can treat a blank coordinate as zero in worksheet arithmetic, so a missing value can look like a valid position. Convert text to numbers before calculating.
- Use one scale for flat coordinates. If X is in meters and Y is in centimeters, convert them to the same unit before measuring the diagonal.
Frequently Asked Questions
These are a few other things you might need to check when working with coordinate distances.
How Do I Calculate GPS Distance in Miles?
Replace 6371 with 3958.76 in the Haversine calculation and change the result header to miles. If you already have a result in kilometers, multiply it by about 0.621371.
How Do I Calculate the Distance Between Two Points in 3D?
Add the squared Z difference to the sum inside SQRT. Use the same units for X, Y, and Z. This applies to Cartesian coordinates, such as positions in a room that include height.
What If My GPS Coordinates Include Degrees, Minutes, and Seconds?
Convert each one to decimal degrees before applying the formula. Start with the degree value without its sign. Add minutes divided by 60 and seconds divided by 3,600. Make the whole result negative for south or west.
Why Is My Distance Different From the Driving Distance?
The GPS calculation estimates the shortest path over Earth’s surface. A driving route follows roads and may have to go around things such as rivers or mountains. Use a routing service if you need mileage for a trip.
Why Is My Result Slightly Different From a Map Calculator?
The Haversine formula here uses one radius for the whole Earth. A calculator based on an ellipsoid, a model that accounts for Earth’s flattening, can return a slightly different distance for the same two points.
Conclusion
I’ve shown you how to estimate distance from GPS coordinates and how to measure between points on a flat grid. Check the type and units of your inputs before you choose a formula. I hope you found this article helpful.
Other Excel articles you may also like:
