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._{1}**x**= Distance of point 2 from x axis._{2}**y**= Distance of point 1 from y axis._{1}**y**= Distance of point 2 from y axis._{2}**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.

**=SQRT((E6-C6)^2 + (F6-D6)^2)**

**Â Â Â Â Â Â Â Â Â Â Â **Here, **G6 **is the starting cell for the distance column. Also, **C6, D6, E6, and F6 **cells indicate the starting cell for **x**** _{1}**,

**x**

**,**

_{2}**y**

**and**

_{1, }**y**

**respectively. Also, here the**

_{2}**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.

**=DistCartesian(C6,D6,E6,F6)**

Here, the arguments of this function are *x*_{1}*, y*_{1}*, x** _{2,}* and

*y*

*respectively.*

_{2}- 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.

**=ACOS(COS(RADIANS(90-C6))*COS(RADIANS(90-E6))+SIN(RADIANS(90-C6))*SIN(RADIANS(90-E6))*COS(RADIANS(D6-F6)))*3959**

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**. Instead, you can*Miles**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.

**=DistGeo(C6,D6,E6,F6)**

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**.