There are several uses for **Excel**. The cool special function and formula in **Microsoft Excel** may determine the separation between two specific cities or locations on the planet. It is crucial to be able to **calculate the distance** between any two places on a map. It enables you to calculate the actual distance and will provide you with a decent estimate of how long your travel will take. But with excel we can easily do that. In this article, we will demonstrate several ways to calculate the distance between two cities in Excel.

**Table of Contents**hide

**Download Practice Workbook**

You can download the workbook and practice with them.

**Dataset Introduction**

To calculate the distance between two cities, first, we need to prepare the dataset. Suppose we have two cities **Los Angeles **which is a major city in the state of **California**, and **Pasco **which is the city of **Washington**. Now, we need to find the distance between them. For this, we must know the** latitude** and **longitude **of both cities. We can simply find any city’s latitude and longitude with Excel. Let’s see how we can do that.

**STEPS:**

- Firstly, select the cell where we put the name of the cities. In our case, we select cells
**B5**, and**B6**. - Secondly, go to the
**Data**tab from the ribbon. - Thirdly, in the
**Data Types**category click on**Geography**.

- Then, to find the latitude, select the cell where you put the latitude and type the formula there.

`=B5.Latitude`

- Press
**Enter**to see the result.

- Similarly, we find the latitude of cell
**B6**. - Now, to find the longitude, likewise, the latitude, select a cell and put the following formula into that cell.

`=B5.Longitude`

- Hit the
**Enter**key on your keyboard.

- Similarly, we get the
**B6**’s longitude by following the earlier steps. - That’s it, now we have the latitude and the longitude of both cities. So, our dataset is now ready to use.

**5 Different Methods to Calculate Distance Between Two Cities in Excel**

Finding the distance between two places will then enable us to determine the separation between two dwellings. We can estimate how long it will take us to travel between homes using this information. To find the distance between two cities we are using the above dataset we have just made.

**1. Apply Haversine Formula to Calculate Distance Between Two Cities**

One of the fundamental equations for oceanic exploration is the **Haversine Formula**, which can be used to calculate the distance between two locations on a spherical plane provided you determine their latitude and longitude and also the radius of the hemisphere. **GPS** devices have essentially automated it. Let’s follow the steps to use the formula to calculate the distance between two cities.

**STEPS:**

- To begin with, select the cell where you want to get the distance, so, we select cell
**C8**. - Then, put the
**Haversine Formula**into that selected cell.

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

- Finally, press the
**Enter**key to see the result.

- So, by using the formula we get the result
**1367.581282**.

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

**2. Compute Distance Between Two Cities with Excel ACOS, SIN, COS, and RADIANS Functions**

The inverse cosine of a value is what **the ACOS function** returns. The sine of an angle in radians is returned by **the Excel SIN function**. The cosine of an angle in radians is returned by **the COS function**. Radians are changed into degrees using **the Excel RADIANS function**. We can combine those functions to get the distance between two cities. It also has been basically automated using **GPS** devices. Let’s apply the combination of those functions to get the distance between two cities by going through the steps.

**STEPS:**

- In the first place, choose the cell where you wish to acquire the distance; in this case, we choose cell
**C8**. - After that, insert the combination of the function’s formula into the chosen cell.

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

- To view the outcome, hit the
**Enter**key at the very end.

- Thus, by applying the formula, we arrive at the number
**1357.033633**.

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

**3. Combine Excel CONCATENATE and SUBSTITUTE Functions to Get Distance Between Two Cities**

To combine two or even more sequences of words into one phrase, use **the text function CONCATENATE**. Approximately **thirty** text pieces can be joined using the Excel **CONCATENATE** function, which returns the result as a text. **The SUBSTITUTE function** of excel uses comparing to modify text in a specified string. We can combine those two functions to create a **Google Map** link to find the **distance between two addresses** using. Let’s utilize the formula to get the distance between two cities by going through the steps.

**STEPS:**

- Likewise, in the previous method, choose the cell where you wish to acquire the
**Google Map**; in this case, we choose cell**C8**. - Then, enter the formula into that selected cell.

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

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

- To display the results, hit the
**Enter**key.

With the help of this formula, you may link to a **Google Map** that shows the route from **Los Angeles** to **Pasco**. The cities will be added to the link using the** CONCATENATE **function, and the names of the cities will be added using the **SUBSTITUTE **function.

- After that, enter this link into your browser’s search box to find out how far apart these two cities are from one another.

The **CONCATENATE** and **SUBSTITUTE** functions may also be used in Excel to determine the distance between two cities on the planet.

**Read More:** **How to Calculate Miles between Two Addresses in Excel (2 Methods)**

**4. Create LAMBDA Function to Calculate Distance Between Two Cities**

**The LAMBDA function** in Excel is used to build customized functions that can be utilized across a worksheet and accessed by familiar names. Once it has been defined and given a name, we can use those functions anywhere in our workbook.

The main equation for the distance is:

`D = acos (sinΦ`

_{1}` . sinΦ`

_{2}` + cosΦ`

_{1 }`. cosΦ`

_{2}` cos`

`Δλ)`

` . R`

Even if it could appear a little overwhelming, the inputs that matter the most to us are:

- The starting point’s latitude and longitude.
- The final location’s latitude and longitude.

So in the formula or the equation.

**Δλ**indicates the difference between the longitudes (**lon_2-lon_1**).**Φ**and_{1 }**Φ**sequentially indicate_{2}**lat_1**and**lat_2**.**R**indicates the radius of the surface.

Let’s create a **LAMBDA** function to calculate the distance between two cities by following the steps down.

**STEPS:**

- First, select cell
**C8**. - Further, put the formula into that cell.

`=LAMBDA(_lat1, _lon1, _lat2, _lon2, LET(lat_1, RADIANS(_lat1),lon_1, RADIANS(_lon1),lat_2,RADIANS(_lat2),lon_2, RADIANS(_lon2),r, 6378,ACOS((SIN(lat_1) * SIN(lat_2)) + (COS(lat_1) * COS(lat_2) * COS(lon_2-lon_1))) * r))(C5,D5,C6,D6)`

- Then, press
**Enter**.

- This will show the result, but the formula is too large and it may be difficult to understand while using this many times.
- So, instead of doing this, we can create a customized formula using the
**LAMBDA**function. - For this, go to the
**Formulas**tab from the ribbon. - Under the
**Defined Names**group, click on**Name Manager**.

- This will display the
**Name Manager**dialog box. - Now, click on the
**New**menu.

- This will take you to the
**New Name**window. - Further, name the formula, as we want to calculate the distance between two cities, so we put the name of the formula
**CityDistance**. - Then, put the following formula into the field of
**Refers to**.

`=LAMBDA(_lat1, _lon1, _lat2, _lon2, LET(lat_1, RADIANS(_lat1),lon_1, RADIANS(_lon1),lat_2,RADIANS(_lat2),lon_2, RADIANS(_lon2),r, 6378,ACOS((SIN(lat_1) * SIN(lat_2)) + (COS(lat_1) * COS(lat_2) * COS(lon_2-lon_1))) * r))`

- Then, click
**OK**.

- This will take you to the
**name Manager**dialog again. - Furthermore, click on the
**Close**button to complete the process.

- Now, you can find a new custom function
**CityDistance**. And by using the function you will get the distance between two cities.

- Thus, by applying the formula, we arrive at the number
**1358.524645**.

**Read More: How to Calculate Levenshtein Distance in Excel (4 Easy Methods)**

**5. Calculate Distance Between Two Cities Using Excel VBA**

Making an **API **(**Application Programming Interface**) connection and utilizing it to build a **User Defined function** in VBA are two other ways to determine the distance between two locations. To gather data for a location, Excel connects through **API** to any map, including **Google Map** and **Bing Map**. Therefore, in order to use this technique, we must first establish an **API** key. That’s really simple, but regrettably, only **Bing Map** offers a free **API**, not Google. We will demonstrate this way by utilizing the free **Bing Map API** key. **Click here** to create your own **Bing Map API Key**.

**STEPS:**

- To begin with, we have created an
**API**key and placed it on cell**C8**.

- Now, go to the
**Developer**tab from the ribbon. - Then, from the
**Code**category, click on**Visual Basic**to open the**Visual Basic Editor**. Or press**Alt + F11**to open the**Visual Basic Editor**.

- Instead of doing this, you can just
**right-click**on your worksheet and go to**View Code**. This will also take you to**Visual Basic Editor**.

- This will appear in the
**Visual Basic Editor**. - Further, click on
**Module**from the**Insert**drop-down menu bar.

- This will create a
**Module**in your workbook.

- And, copy and paste the
**VBA code**shown below.

**VBA Code:**

```
Option Explicit
Public Function CityDistance(First_City As String, Second_City 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=km"
Set Setup_HTTP = CreateObject("MSXML2.ServerXMLHTTP")
Output_Url = Initial_Point & First_City & Ending_Point & Second_City & 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 ("")
CityDistance = Round(Round(WorksheetFunction.FilterXML(Setup_HTTP.ResponseText, _
"//TravelDistance"), 3), 0)
End Function
```

- Save the code by saving the whole workbook as a micro-enabled workbook and the extension will be
**.xlsm**.

- This will create a user-defined function name
**CityDistance**.

**VBA Code Explanation**

- We started by giving our
**Function**the name**CityDistance**.**First City**As String,**Second City**As String, and**Target Value**As String were also included as arguments. - Then we declared
**Setup HTTP**as an object and**Initial Point**,**Ending Point**,**Distance Unit**, and**Output Url**as Strings. - Later, we changed the
**Distance Unit**to**kilometer**and**Initial Point**to the starting point of the**Url**link. - Next, we set the parameters required to establish a connection between our
**VBA code**and the**API**. - We then created our
**User Defined Function**.

**Conclusion**

The above methods will assist you in **Calculate Distance Between Two Cities** **in Excel**. Hope this will help you! Please let us know in the comment section if you have any questions, suggestions, or feedback. Or you can have a glance at our other articles in the **ExcelDemy.com** blog!