How to Calculate Distance Between Two GPS Coordinates in Excel

To showcase the methods, the sample will use a simple dataset that lists the Latitude and Longitude values of Prague, Czech Republic, and Salzburg, Austria.

Method 1 – Using an Arithmetic Formula to Calculate Distance between Two GPS Coordinates

Steps:

Create a new row titled Distance (Miles).
Select the result cell to apply the following formula:

=ACOS(COS(RADIANS(90-C5))*COS(RADIANS(90-C6))+SIN(RADIANS(90-C5))*SIN(RADIANS(90-C6))*COS(RADIANS(D5-D6)))*3959

Formula Breakdown

The RADIANS function converts the value in Degree units into a value of Radian unit.
The ACOS function returns the inverse cosine of a number.

COS(RADIANS(90-C5))*COS(RADIANS(90-C6))+SIN(RADIANS(90-C5))*SIN(RADIANS(90-C6))*COS(RADIANS(D5-D6)) – this portion provides the value using trigonometry operators.
Output: 0.999092512926254

ACOS(0.999092512926254) – ACOS function returns the inverse Cosine value.
Output: 0.0426057358212635

0.0426057358212635 * 3959 – The multiplication of 3959 converts the value into Miles.
Output: 168.676108116382

Press ENTER to get the result.

Method 2 – Using VBA to Calculate Distance between Two GPS Coordinates

Steps:

Go to the Developer tab.
Choose Visual Basic from the ribbon.

Click on Insert.
Choose Module.

Paste the following VBA code in the editor:

Public Function DistCalc(Prague_Lati As Double, Prague_Longi As Double, Salzburg_Lati As Double, Salzburg_Longi As Double)
    With WorksheetFunction
        M = Cos(.Radians(90 - Prague_Lati))
        N = Cos(.Radians(90 - Salzburg_Lati))
        O = Sin(.Radians(90 - Prague_Lati))
        P = Sin(.Radians(90 - Salzburg_Lati))
        Q = Cos(.Radians(Prague_Longi - Salzburg_Longi))
'Change 6371 to 3959 to get your result in Miles
        DistCalc = .Acos(M * N + O * P * Q) * 6371
    End With
End Function

Select Save and return to the sheet.
Select a cell to have the measured result (i.e. C8).
Apply the following formula:

=DistCalc(C5,D5,C6,D6)

Here, the new DistCalc function which was VBA coded estimates the distance between the two points and can be called up by Excel similarly to any other function you use.