How to Calculate Distance Between Two Coordinates in Excel (2 Methods)

In many cases, you may need to find distance between two coordinates or two locations. In Microsoft Excel you can do this in a bulk size within a short duration. This article demonstrates two methods to calculate distance between two coordinates in Excel.

Download Practice Workbook

You can download the practice book from the link below.

Basics of Coordinate System

Coordinate system is a system, particularly in geometry, that uses one or more numbers to locate the position of a point or to calculate other geometric parameters. There are many kinds of coordinate systems. For example: Cartesian Coordinate System, Geodetic Coordinate System etc.

What Is Cartesian Coordinate System?

Cartesian Coordinate System is a system which uses reference axes to locate any position or calculate any geometrical data. The coordinates of a point are determined from the distance of those reference axes. For example:

In a 2-D plane X-axis indicates the horizontal plane and Y-axis indicates the vertical plane. So, if someone gives the coordinates of a point (2,3) it means the point is 2 units from the horizontal plane and 3 units from the longitudinal plane.

Distance Formula for Cartesian Coordinate System

The arithmetic formula for calculating distance in a 2-D Cartesian Coordinate System is as follows:

d=√((x2-x1)^2+(y2-y1)^2)

Here,

• x₁ = Distance of point 1 from x axis.
• x₂ = Distance of point 2 from x axis.
• y₁ = Distance of point 1 from y axis.
• y₂ = Distance of point 2 from y axis.
• d = Distance between point 1 and point 2.

What Is Geodetic Coordinate System?

Geodetic Coordinate System is a type of coordinate system that uses an ellipsoid as reference to locate the position of a point and measure other geometric parameters. We commonly use latitude and longitude to locate the position in this coordinate system.

Here, latitude means the distance in the north or south direction from the equator and longitude means the distance in east or west direction from the prime meridian. Also, positive values of latitude and longitude mean north and east and negative values mean south and west respectively.

Distance Formula for Geodetic Coordinate System

The arithmetic formula to calculate distance between two coordinates in Geodetic Coordinate System is as follows:

d=acos(cos(radian(90-lat1))*cos(radian(90-lat2))+

           sin(radian(90-lat1))*sin(radian(90-lat2)*cos(radian(long1-long2)))*3959

Here,

• lat1 =  Latitude of location 1
• lat2 = Latitude of location 2
• long1 = Longitude of location 1
• long2 = Longitude of location 2
• d =  Distance between location 1 and location 2 in miles

2 Methods to Calculate Distance Between Two Coordinates in Excel (Cartesian Coordinate System)

Suppose, you have a dataset where you have coordinates of point 1 and point 2 in different situations and you want to calculate the distance between them. Now, I will show you two ways to do so.

1. Using Arithmetic Formula to Calculate Distance

You can easily calculate the distance by inserting the arithmetic formula manually. Now, follow the steps below to calculate the distance.

Steps:

• First, create a column next for Distance.
• Next, select cell G6 and insert the following formula.

            Here, G6 is the starting cell for the distance column. Also, C6, D6, E6, and F6 cells indicate the starting cell for x₁, x₂, y_1, and y₂ respectively. Also, here the SQRT function is used to find the square root.

• Lastly, drag the Fill handle for the rest of the column and you get your distances.

2. Applying VBA Code to Calculate Distance Between Two Coordinates in Excel

You can also use VBA code to create a function for calculating distance in a cartesian coordinate system and then use it for calculation. Now, follow the steps below to do so.

Steps:

• First, press ALT + F11 to open the VBA window.
• Now, select This Workbook and Right-Click on it.
• Next, sequentially select Insert > Module.

• At this point, copy the following code and paste it into the blank box.

'This Code will Create A function for Calculating Distance in Cartesian Coordinate System
Public Function DistCartesian(x1 As Double, y1 As Double, x2 As Double, y2 As Double)
'We are using the arithmetic formula for calculating distance to create the function
A = (x2 - x1) ^ 2 + (y2 - y1) ^ 2
DistCartesian = Math.Sqr(A)
End Function

• After that, press F5 to run the code. Here, this code has created a new function DistCartesian for you which will help you calculate the distance between two coordinates in a cartesian plane.
• Now, select cell G6 and insert the following formula.

Here, the arguments of this function are x₁, y₁, x_2, and y₂ respectively.

• Finally, drag the Fill handle for the remaining cells.

Read More: How to Calculate Distance between Two GPS Coordinates in Excel

2 Methods to Calculate Distance Between Two Coordinates in Excel (Geodetic Coordinate System)

Suppose, in the Geodetic Coordinate System, you have the latitude and longitude of two different locations. Now, follow the steps below to calculate the distance between those two locations.

1. Using Arithmetic Formula to Calculate Distance

One of the fastest ways to calculate the distance is putting the arithmetic formula for calculating distance manually. Now, follow the steps below to calculate the distance between two coordinates in the Geodetic Coordinate System.

Steps:

• First, add a column for Distance (Miles).
• Then, select cell G6 and insert the following formula.

Here, C6, D6, E6, and F6 indicate the first cells for the column of Latitude 1 (°), Longitude 1 (°), Latitude 2 (°), and Longitude 2 (°) respectively.

⧬ Formula Explanation 

In this formula:

• RADIANS function is used to convert a value in the Degrees (°) unit to a value in the Radian unit.
• COS function is used to find the cosine of an angle.
• SIN function is used to find the sine of an angle.
• ACOS function is used to return the arccosine or inverse cosine of a number.
• Lastly, the number 3959 is multiplied to get the distance in Miles. Instead, you can multiply by 6371 to get the result in Kilometers.

• Finally, drag the Fill handle for the rest of the column to get your distances.

Read More: How to Calculate Distance Between Two Cities in Excel

2. Applying VBA Code to Calculate Distance Between Two Coordinates in Excel

You can also use VBA code to create a function for calculating distance in a Geodetic Coordinate System and then use it for calculation. Now, follow the steps below to do so.

Steps:

• First, press ALT + F11 to open the VBA window.
• Now, select This Workbook and Right-Click on it.
• Next, sequentially select Insert > Module.

• After inserting the module, copy the following code and paste it in the blank space.

'This Code will create a function to calculte distance(miles) in geodetic coordinate system
Public Function DistGeo(Lati1 As Double, Longi1 As Double, Lati2 As Double, Longi2 As Double)
    With WorksheetFunction
        P = Cos(.Radians(90 - Lati1))
        Q = Cos(.Radians(90 - Lati2))
        R = Sin(.Radians(90 - Lati1))
        S = Sin(.Radians(90 - Lati2))
        T = Cos(.Radians(Longi1 - Longi2))
'Change 3959 to 6371 to get your result in KiloMeter
        DistGeo = .Acos(P * Q + R * S * T) * 3959
    End With
End Function

• At this point, press F5 to run the code. By doing so, you will create a new function DistGeo which will help you calculate the distance in a Geodetic Coordinate System.
• After that, select cell G6 and write down the following formula.

Here, the arguments of the DistGeo function are Latitude 1 (°), Longitude 1 (°), Latitude 2 (°), and Longitude 2 (°) respectively.

• Finally, drag the Fill handle for the remaining cells of the column.

Read More: How to Calculate Driving Distance between Two Addresses in Excel

Conclusion

Last but not the least, I hope you found what you were looking for from this article. If you have any queries, please drop a comment below. If you want to read more articles like this, you can visit our website ExcelDemy.

Related Articles

• Calculate Coordinates from Bearing and Distance in Excel
• How to Calculate Mahalanobis Distance in Excel (Step by Step)
• Calculate Manhattan Distance in Excel (2 Suitable Ways)
• How to Calculate Levenshtein Distance in Excel (4 Easy Methods)