The article will show you how to calculate the distance between two addresses in Excel. Microsoft Excel has this cool special feature and formula for finding the distance between two places in the world. If you have GPS coordinates of two places, you can easily determine the distance between those places in any unit you want.

Here is an overview of how the distance between two addresses can be found in Excel.

## How to Calculate Distance Between Two Addresses in Excel: 3 Ways

In our dataset, we have **Latitude **and **Longitude **for Ohio and Alaska. We are going to measure the distance between them.

The **Latitudes **for Ohio and Alaska are **40.4173 North **and **64.2008 North **respectively. Also, the **Longitudes **for Ohio and Alaska are **82.9071 West **and **149.4937 West **respectively. As the longitudes are westward, they are the negative angles.

**1. Using Haversine Formula to Calculate Distance Between Two Addresses **

If you are good at mathematics, using **Haversine Formula **will be perfect for you. Although it will give you an approximate result, it’s a pretty good one.

First of all, we need to know the basic formula of arc length. And using that formula, we compare it with the distance between two places on earth. The formula is given below.

**S = rθ**

**S = Distance between two addresses**

**r = Radius of Earth**

** θ = Angle introduced at the center of the Earth by the two addresses**

But if you have **GPS** coordinates of two places, you need to determine from the **Haversine Formula**. Look at the **Haversine Formula **shown below.

If you compare this with the **arc length formula**, you will get like in the following image.

Let’s introduce you to the parameters of the **Haversine Formula**.

**φ _{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, I’ll be showing you how to apply this formula in Excel step by step.

**Steps:**

- First, make a cell to store the distance value and type the following formula in cell
**C8**.

`=2*6400*ASIN(SQRT((SIN(RADIANS((C6-C5)/2)))^2+COS(RADIANS(C5))*COS(RADIANS(C6))*(SIN(RADIANS((D6-D5)/2)))^2))`

The formula uses **ASIN**, **RADIANS**, **SQRT**, **SIN**, and **COS** functions. It’s pretty simple if you just look at the **Haversine Formula**. We measure the distance in kilometers, so we put the radius of the earth in kilometers which is **6400 km**. **ASIN **refers to the **inverse Sine **or the **ArcSine**. If we compare the parameter angles of the **Haversine Formula **with our Excel formula, we get,

**φ _{1} = Latitude of Ohio (C5)**

**φ _{2} = Latitude of Alaska (C6)**

**ℽ**_{1}** = Longitude of Ohio (D5)**

**ℽ**_{2}** = Latitude of Alaska (D6)**

- After that, press the
**ENTER**button to see the distance between**Ohio**and**Alaska**in**Kilometers**. - Thereafter, if you want to measure the distance in miles, use the following formula in cell
**C8**.

`=2*3959*ASIN(SQRT((SIN(RADIANS((C6-C5)/2)))^2+COS(RADIANS(C5))*COS(RADIANS(C6))*(SIN(RADIANS((D6-D5)/2)))^2))`

Here, we used the same Excel formula we used to find the distance in miles. For that reason, we take the earth’s radius in miles which is 3959.

Thus you can calculate the distance between two addresses of earth in Excel by applying the **Haversine Formula**.

There is a Cosine version of this formula which is simpler to use. See the formula and image below.

`=ACOS(SIN(RADIANS(C5)) *SIN(RADIANS(C6)) +COS(RADIANS(C5)) *COS(RADIANS(C6)) *COS(RADIANS(D5-D6))) *DISTANCE`

Put **DISTANCE = 6371 or 6400 **if you want to find the distance in kilometers. On the other hand, set the value of **DISTANCE **to **3959 **if you want to measure the distance in miles.

**Note: **Keep in mind that the formula is derived assuming the earth having a spherical shape, while it’s an irregularly shaped ellipsoid. So if you compare the distance obtained by the formula with Google Map or other online app results, you may find deviation. To get accurate result, please go through the other methods.

**2. Using VBA to Calculate Distance Between Two Addresses in Excel**

Another way to calculate the distance between two addresses could be by making an **API** (**Application Programming Interface**) link and using it to create a **User Defined function** by **VBA**. You can make an **API **link in **Bing **for free. To create a **Bing Map** API Key of your own, click here. Let’s follow the instructions below to calculate the distance between two addresses or coordinates using Excel VBA code.

**Steps:**

- First, go to
**Developer**>>**Visual Basic**.

- After that, select
**Insert**>>**Module**to open the**VBA Module**.

- Later, type the following code in the
**VBA Module**. We are creating an**User Defined Function**to calculate distance in miles.

```
Option Explicit
Public Function DistanceInMiles(First_Location As String, _
Final_Location As String, Target_Value As String)
Dim Initial_Point As String, Ending_Point As String, _
Distance_Unit As String, Setup_HTTP As Object, Output_Url As String
Initial_Point = "https://dev.virtualearth.net/REST/v1/Routes/DistanceMatrix?origins="
Ending_Point = "&destinations="
Distance_Unit = "&travelMode=driving&o=xml&key=" & Target_Value & "&distanceUnit=mi"
Set Setup_HTTP = CreateObject("MSXML2.ServerXMLHTTP")
Output_Url = Initial_Point & First_Location & Ending_Point & Final_Location & Distance_Unit
Setup_HTTP.Open "GET", Output_Url, False
Setup_HTTP.SetRequestHeader "User-Agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)"
Setup_HTTP.Send ("")
DistanceInMiles = Round(Round(WorksheetFunction.FilterXML(Setup_HTTP.ResponseText, _
"//TravelDistance"), 3), 0)
End Function
```

**Code Explanation**

- First, we named our
**Function**as**DistanceInMiles**. We also inserted**3**parameters:**First_Location As String, Final_Location As String**and**Target_Value As String**. - After that, we declared
**Initial_Point**,**Ending_Point**,**Distance_Unit**and**Outout_Url**as**String**;**Setup_HTTP**as**Object**. - Later, we set
**Initial_Point**as the starting of the**Url link**,**Ending_Point**as**Destination**and**Distance_Unit**to**Miles**. - After that, we set the necessary parameters to create a relation between our
**VBA**code and the**API** - Finally, we established our
**User Defined Function**.

- After that, following the same procedures, we made another
**User Defined Function**to find the distance in**kilometers**.

You can see that, we just changed the **Distance_Unit **to **kilometers**.

- Thereafter, in the following picture, you can see the
**API**key in cell**C8**.

- After that, type the following formula in cell
**C8**.

`=DistanceInMiles(E5,E6,C8)`

- Next, press the
**ENTER**button and you will see the**distance**between**Ohio**and**Alaska**in**miles**.

- After that, use the following formula to see the distance in kilometres.

`=DistanceInKM(E5,E6,C8)`

Thus, you can calculate the distance between two addresses using **VBA **and **API **key.

**Note: **The **API **key doesn’t work long. It expires someday and so you need to update it or create a new one. Here is a new **API **key that you can use to find out distances.

`AvAMQVCQZRHasj75zfHQyX5lHvfgjJKInhHyaY6cXJtXsulSdMC3r71LGZ2uTY3a`

Moreover, this method works properly for the locations of USA, in my experience.

**3. Applying Excel CONCATENATE and SUBSTITUTE Functions to Calculate Distance Between Two Addresses**

You can create a **Google Map **link to find the distance between two addresses using Excel **CONCATENATE** and **SUBSTITUTE** Functions. Let’s go through the process below.

**Steps:**

- First, type the following formula in
**C8**.

`=CONCATENATE("http://maps.google.co.uk/maps?f=d&source=s_d&saddr=",`

`SUBSTITUTE(B5," ","+"),"&daddr=",SUBSTITUTE(B6," ","+"))`

This formula will create a **Google Map **link of how you can travel from Ohio to Alaska. The** CONCATENATE **function will add the addresses in the link and the** SUBSTITUTE **function will place the name of the addresses.

- Next, press the
**ENTER**button and you will see the**Google Map**link in**C8**.

- After that, use this
**link**in your internet search bar and you will get the distance information about these two addresses.

Finally, you can calculate the distance between two addresses of the earth in Excel by applying **CONCATENATE **and **SUBSTITUTE **functions.

**Note: **The method creates google map link and if the distance or route cannot be determined by google, you will not get the distance between the two addresses.

## How to Calculate ZIP Code Distances in Excel

Here, I’ll show you a way of calculating the distance between **ZIP **codes. You need to add an Excel **Add-in **for this purpose. You can get the **GeoData** **Add-in **and necessary **API **key in the CDXGeoData Website.

After you download the **Add-in**, you need to select **File tab **>> **Options** >> **Add-ins **and then click on the **Go **button beside the **Manage **section. Next, browse for the downloaded **Add-in **and click **OK**.

- Now, you need to insert the
**API**key in the**Settings**window under the**CDXGeodata**tab which is added after installing the**GeoData Add-in**.

- Finally, insert some valid
**ZIP**codes and use the formula below to calculate the distance between them.

`=GeoDistance(B5,C5)`

**Note: **The **API **key will be valid for **24 **hours and the formula can be used **100 **times for free version.

**Things to Remember**

- Keep that in mind, you need to put approximately correct
**GPS Coordinates**of these states in the data because you are using the internet to find the distance. As the states are west to the**Meridian Line**, both of their**Longitudes**will be negative.

## Practice Section

In this section, I’ll give you the dataset of this article so that you can practice these methods on your own.

**Download Practice Workbook**

## Conclusion

Suffice to say, you will learn very effective methods of how to calculate distance between two addresses in Excel after reading this article. If you have any better methods or questions or feedback regarding this article, please share them in the comment box. This will help me enrich my upcoming articles.

## Frequently Asked Questions

**1. Can I calculate distances without using APIs?**

Answer: It is difficult to compute distances effectively without the use of APIs since you would require access to large geographic datasets and algorithms for calculating distances based on addresses. APIs offer a more convenient and dependable solution.

**2. Are there any Excel add-ins or plugins for calculating distances?**

Answer: Yes, third-party add-ins or plugins that interact with Excel and provide distance computation features may be available. These could help to streamline the procedure and eliminate the need for lengthy coding.

**3. What precautions should I take when using APIs in Excel?**

Answer: When using APIs, make sure to follow the API provider’s terms of service. Also, keep security in mind, especially when dealing with API keys. Keep your API keys in a secure location and avoid storing them in public files.

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

Isn’t Haversine Formula required an angle in rad unit?

Hello L.C., thank you for reaching out. The angles in the formula are used as input for the Sine and Cosine functions. So the conversion from degree to radian isn’t necessary. Whether I change the angle unit to radian or not, the value of the corresponding Sine or Cosine function will be the same as the angles remain the same.

How can you run this script for 100s of comparison for two locations data in a sheet.

Sheet 1 has 100+ locations (let’s say A,B,C,D… ) latitude and longitude and Sheet 2 has 100+ locations (say 1, 2, 3, 4 5…) with latitude and longitude.

I need to find the nearby/identical locations to each other. For example how far is A from 1, 2, 3, 4, … and how far is B from 1,2,3,4, …. and so on … how would you accomplish that ?

Use case: A sales guy can cover and talk to all customers in an area in one go.

Hello AC, thanks for reaching out. Here’s a solution to your problem.

Here, I have some addresses in two sheets. The addresses in the first sheet are in the left side of the following image. The addresses of the second sheet can be found in the drop down list.

And here is the formula to calculate the distance.

`=ACOS(SIN(RADIANS(VLOOKUP(F3,Sheet2!A2:C33,2,FALSE))) *SIN(RADIANS(VLOOKUP(E3,A2:C29,2,FALSE))) +COS(RADIANS(VLOOKUP(F3,Sheet2!A2:C33,2,FALSE))) *COS(RADIANS(VLOOKUP(E3,A2:C29,2,FALSE))) *COS(RADIANS(VLOOKUP(F3,Sheet2!A2:C33,3,FALSE)-VLOOKUP(E3,A2:C29,3,FALSE)))) *6371`

This is really great, thank you for posting this. Do you know of a way to modify the script to use the address string itself (rather than the Lat/Long coordinates)? Or do you know of an easy way to get Lat/Long coordinates from an address? Still researching this myself, but figured I’d ask! Thanks again.

-Dan

Hello

DAN HThanks for your nice words. Your appreciation means a lot to us. Thanks once again for sharing your requirements with such clarity.

You wanted to get an Excel VBA Sub-procedures or Excel VBA User-defined functions to calculate the distance between two addresses using

Address String(for example,New York,Alaska) instead of Latitudes and Longitudes values.I am delighted that I developed some

Excel VBA User-definedfunctions to fulfil your requirements.OUTPUT OVERVIEW:Follow these steps:Step 1: Hover overInsert=> Click onModule=> paste the following code in the module.Step 2: Select cellC9=> Insert the following formula.Step 3: HitEnterto see the result in cell C9.Step 4: Select cellC10=> Insert the following formula.Step 5: HitEnterto see the result in cellC10.Things to Keep in Mind: Ensure to checkMicrosoft XML, v3.0from theReferences – VBAProjectwindow.I am attaching the solution workbook. Hopefully, these user-defined functions will help you reach your goal. Good luck.

DOWNLOAD WORKBOOKRegards

Lutfor Rahman ShimantoExcel & VBA Developer

ExcelDemy

Hello,

I tried out the VBA & API (Bing) option and combined with a rate/mile finder tool.

It works perfectly on my PC and in the UK from where I the price list received.

How can I change cultural settings in VBA so it works also on non-UK clients? Because currently if it’s running on a non-UK client for the distance result it gives #VALUE error. Altough I disabled using system separators and set them manually Thank you!

Hello

MEDOThanks for letting us know that the solution works perfectly on your end. However, you want to handle cultural settings in VBA, which also works on non-UK clients.

Assuming your requirements are related to the

decimal separatorandthousand separatorsettings. Different regions worldwide use different conventions to represent decimals and thousands of separators.You can use two user-defined functions to replace these separators with

periodsandcommasto overcome the situation. I have developed two VBA user-defined functions and slightly changed the existing sub-procedures.Calculating Distance in Miles:Calculating Distance in KM:Hopefully, the VBA code will help you with your problem; good luck.

Regards

Lutfor Rahman ShimantoExcel & VBA DeveloperExcelDemy