In this article, we will learn how to find distance using formulas in Excel. We will apply the basic arithmetic formula, the Euclidean formula, the Haversine formula, and a VBA user-defined function (UDF) to calculate the distance in Excel. We will use Cartesian coordinates for point distance and GPS coordinates for the distance between two places.

While preparing this article, we used Excel 365 for applying all operations, but they are also applicable in all Excel versions.

In our daily life, we frequently need to calculate the distance between two points or places. We can easily calculate distance using the latitude and longitude of two places. It helps to determine the distance between two geographic locations. We can also calculate distance using cartesian coordinates.

This is an image containing the GPS coordinates of two different states in the USA. It also contains the Haversine formula. We will use this formula to get the distance between these two places.

## How to Find Distance Using Formula in Excel?

There are four ways to find distance using formula in Excel. Read the methods below to learn all of them.

### 1. Calculating Distance Using the Basic Arithmetic Formula

To calculate the distance in a cartesian coordinate system, we can apply **the SQRT function** in Excel.

Follow the steps below to do it yourself.

- We will measure the distance between two points using the basic arithmetic formula. We have a dataset of a few points with their
**Cartesian**coordinates.

- Now, we will insert the formula with
**the SQRT function**in cell**G6**and drag down the**Fill Handle**tool.

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

- Finally, you will get the distance between each pair of points.

### 2. Calculating Euclidean Distance in Excel

The distance between two vectors is known as the Euclidean distance. The formula to find the Euclidean distance is:

Euclidean distance = **√Σ(X-Y)2**

Here,

**Σ** Greek sign means Total Sum.

X is the value in vector point 1.

Y is the value in vector point 2.

We can use this formula in the following format in Excel.

`=SQRT(SUMXMY2(ARRAY 1, ARRAY 2))`

Follow the next steps to calculate the distance using the Euclidean formula.

● In the beginning, we have a dataset with few vector points.

● Next, insert the following Euclidean formula in cell C11.

`=SQRT(SUMXMY2(B5:B10,C5:C10))`

**Formula Breakdown**

● **The SUMXMY2 function** returns the sum of squares of differences of corresponding values in two arrays. Here, it returns that for Point 1 and Point 2 arrays.

● **The SQRT function** provides the square root value of a number. Here, it will return the square root value of the sum.

- Finally, you will see the Euclidean distance in cell
**C11**.

**Note:** This function calculates only pairwise data, it ignores single values in the array.

### 3. Finding Distance Between Two Coordinates Applying the Haversine Formula

The Haversine formula calculates the great circle distance between two points on the surface of a sphere. We can calculate the distance between two places using this formula.

Let’s go through the Haversine Formula’s parameters.

**φ1** = Latitude of the first place

**φ2** = Latitude of the second place

**ℽ1** = Longitude of the first place

**ℽ2 **= Latitude of the second place

Now, follow the steps mentioned below to find the distance using the Haversine formula.

- First, we have a dataset containing the
**GPS**coordinates of two addresses.

- Now, insert the following Haversine formula in any other cell (here, cell
**D8**).

**Formula Breakdown**

**ASIN**: The**ASIN**function gives the arcsine of the inverse sine of a value.**SQRT**: The**SQRT**function returns the square root of any number.**RADIANS:**The**RADIANS**function converts degree values to radians.**SIN:****The SIN function**calculates the sine of a value. The**SIN**function is used twice in the formula.**SIN(C6-C5)**returns the sine of the difference between the values in cells**C6**and**C5**.**SIN(D6-D5)**gives the sine of the difference between the values in cells**D6**and**D5**.**COS:****The COS function**calculates the cosine of a value. Here it is used to calculate:**COS(C5)**and**COS(C6)****Arithmetic operations:**The formula includes several arithmetic operations:**/**represents division.**^2**represents squaring a value.*****represents multiplication.

**Constants**: The formula has two constant values. One is number**2**. Another one is**6400**, which is the radius of Earth in kilometers.

**Note:** *Remember that the formula was developed under the assumption that the earth had a spherical shape, even though it has an elliptical shape. Therefore, there may be a difference between the distance determined by the method and the findings of Google Maps or other web apps. Please use the other methods to obtain accurate results.*

### 4. Creating UDF to Calculate Distance Using VBA Code

Yes, we can create a user-defined function(UDF) in VBA to calculate distance in Excel. Read the steps to learn that.

- Here, we have the
**GPS**coordinates of a few locations.

- Now, we will open the
**Developer**tab and select the**Visual Basic**command.

- After that, from the
**Insert**option, we will choose the new**Module**to write the VBA code.

- Now, we will insert the VBA code and run the module as mentioned below.

```
Public Function CalculateDistance (Latitude1 As Double, Longitude1 As Double, _
Latitude2 As Double, Longitude2 As Double) As Double
With WorksheetFunction
Dim P As Double
Dim Q As Double
Dim R As Double
Dim S As Double
Dim T As Double
P = Cos(.Radians(90 - Latitude1))
Q = Cos(.Radians(90 - Latitude2))
R = Sin(.Radians(90 - Latitude1))
S = Sin(.Radians(90 - Latitude2))
T = Cos(.Radians(Longitude1 - Longitude2))
' Change 3959 to 6371 to get the result in kilometers
CalculateDistance = .Acos(P * Q + R * S * T) * 3959
End With
End Function
```

**Code Breakdown**

`Public Function CalculateDistance (Latitude1 As Double, Longitude1 As Double, Latitude2 As Double, Longitude2 As Double) As Double`

This line begins a public function named *CalculateDistance* that takes four input parameters: Latitude1, Longitude1, Latitude2, and Longitude2, all of which are Double data types. The function will return the distance between the two points as a Double data type.

`With WorksheetFunction`

This line begins a **With** a block that allows us to access built-in worksheet functions using the **WorksheetFunction** object.

```
P = Cos(.Radians(90 - Latitude1))
Q = Cos(.Radians(90 - Latitude2))
R = Sin(.Radians(90 - Latitude1))
S = Sin(.Radians(90 - Latitude2))
T = Cos(.Radians(Longitude1 - Longitude2))
```

These codes assign values to the variables P, Q, R, S, and T. Here, the code uses trigonometric functions and the Radians methods to convert degrees to radians.

`CalculateDistance = .Acos(P * Q + R * S * T) * 3959`

The code calculates the distance between the two points using the Haversine formula. The result is assigned to the *CalculateDistance* variable.

The **Acos function** returns the inverse cosine of the total of P * Q and R * S * T.

Now multiply the value by 3959 to get the distance in miles. You can adjust 3959 to 6371 to get the answer in kilometers.

- Now, apply the
**UDF**(user-defined function) in the dataset.

- Now, drag down or double-click the
**Fill Handle**tool to copy the VBA function to the bottom cells.

## Which Things You Have to Keep in Mind?

- Make sure the coordinates are formatted correctly. You can use both Geodetic and Cartesian coordinates.
- Ensure that the units of the coordinate are consistent. If you use different units (for example, feet and meters), the calculated distance will not be accurate. Convert the coordinates to the same unit before using the formula.
- Maintaining the order of the points in the formula is important. Suppose the formula is in the following order: (x1, y1) and (x2, y2). If you reverse the order, the result will be inaccurate.

**Download Practice Workbook**

## Conclusion

In this article, we have shown how to find the distance between two points or places using different formula in Excel. We used the basic mathematical formula, Euclidean formula, Haversine formula, and VBA. We hope now you will be able to calculate distance in Excel. Practice these formulas by yourself and become an expert in Excel! If you have any questions, comments, or recommendations, kindly leave them in the comment section below.

## Frequently Asked Questions

**1. Can I calculate the distance between two points in different worksheets?**

**Answer:** You can calculate the distance between two points on different worksheets. In the distance formula, reference the coordinates’ cells from the appropriate worksheets.

**2. How can I calculate the distance between multiple points in Excel?**

**Answer: **You can find the distance between two points by using the distance formula with the array formula or helper columns.

**3. Is there a built-in function in Excel to calculate distances between coordinates?**

**Answer:** Excel doesn’t have a built-in function for calculating distances between coordinates. However, you can calculate distances using the distance mentioned above, which combines functions such as **SQRT** and **POWER**.

**Distance Formula in Excel: Knowledge Hub**

- Calculate Euclidean Distance
- Find Manhattan Distance
- Calculate Mahalanobis Distance
- Calculate Hamming Distance
- Measure Distance in Excel with Google Maps
- Calculate Driving Distance between Two Addresses
- Measure Distance between Two Addresses or Coordinates
- Calculate Coordinates from Bearing and Distance

- Find Distance between Zip Codes
- Calculate Levenshtein Distance
- Find Great Circle Distance
- Calculate Distance between Two GPS Coordinates
- Find Distance between Two Addresses in Excel
- Calculate Miles between Two Addresses
- Measure Distance between Two Cities in Excel
- Determine Distance between Two Coordinates

**<< Go Back to Formula List | Learn Excel**