How to Calculate Distance Between Two Cities in Excel

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.


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.

Apply Haversine Formula to Calculate Distance Between Two Cities

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

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

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

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

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.

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

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).
  • Φ1 and Φ2 sequentially indicate 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.

Create LAMBDA Function to Calculate Distance Between Two Cities

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

Create LAMBDA Function to Calculate Distance Between Two Cities

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

Calculate Distance Between Two Cities Using Excel VBA

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

Calculate Distance Between Two Cities Using Excel VBA

  • 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!


Related Articles

Sabrina Ayon

Sabrina Ayon

Hi there! This is Sabrina Ayon. I'm really excited to welcome you to my profile. Currently, I'm working in SOFTEKO as a Excel and VBA Content Developer. I'm a student of United International University and my program is Computer Science and Engineering. I love working with computers and solving problems. I’ve always been interested in research and development. So, here I will be posting articles related to Microsoft Excel. Hoped this may help you. Thank you.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo