Dataset Overview
To calculate the distance between two cities, we’ll start by preparing the dataset. Let’s consider two cities: Los Angeles (a major city in California) and Pasco (located in Washington). Our goal is to find the distance between these two cities. For this, we’ll need the latitude and longitude coordinates of both cities. Fortunately, we can easily obtain this information using Excel. Let’s walk through the steps:
Create a Dataset
 Choose the cells where you want to input the city names. In our example, we’ll select cells B5 and B6.
 Go to the Data tab on the Excel ribbon.
 Under the Data Types category, click on Geography.
 Select the cell where you want to display the latitude (e.g., B5).
 Enter the following formula:
=B5.Latitude
 Press Enter to see the latitude value.
 Similarly, find the latitude for the second city (e.g., B6).
 Select a cell where you want to display the longitude (e.g., B5).
 Use the following formula:
=B5.Longitude
 Press Enter to get the longitude value.
 Follow the same steps to find the longitude for the second city (e.g., B6).
 Now you have the latitude and longitude coordinates for both cities. Your dataset is ready for further calculations.
Method 1 – Applying the Haversine Formula to Calculate the Distance Between Two Cities
The Haversine Formula is a fundamental equation used in oceanic exploration. It allows us to calculate the distance between two locations on a spherical plane, provided we know their latitude, longitude, and the radius of the hemisphere. GPS devices have automated this process. Let’s follow the steps to use the formula for calculating the distance between two cities:
Steps
 Choose the cell where you want to display the distance. In our example, we’ll select cell C8.
 Enter the following formula into the selected cell:
=3959*ASIN(SQRT((SIN(C6C5)/2)^2+COS(C5)*COS(C6)*(SIN(D6D5)/2)^2))
 Press Enter to see the result.
 By using the formula, we obtain the result 1367.581282 (approximately).
Method 2 – Computing the Distance Between Two Cities with Excel’s ACOS, SIN, COS, and RADIANS Functions
In this method, we’ll combine several Excel functions to calculate the distance between two cities. These functions include:
 ACOS: Returns the inverse cosine of a value.
 SIN: Returns the sine of an angle in radians.
 COS: Returns the cosine of an angle in radians.
 RADIANS: Converts radians to degrees.
Steps
 Choose the cell where you want to display the distance (e.g., cell C8).
 Enter the following formula into the chosen cell:
=ACOS(COS(RADIANS(90C5)) *COS(RADIANS(90C6)) +SIN(RADIANS(90C5)) *SIN(RADIANS(90C6)) *COS(RADIANS(D5D6))) *6371
 Press Enter to view the outcome.
 By applying the formula, we arrive at the distance of approximately 1357.033633.
Method 3 – Combining Excel’s CONCATENATE and SUBSTITUTE Functions to Get the Distance Between Two Cities
In this method, we’ll use the CONCATENATE and SUBSTITUTE functions in Excel to create a Google Map link that shows the route between two addresses.
Steps
 Choose the cell where you want to display the Google Map link. In our example, we’ll select cell C8.
 Enter the following formula into the selected cell:
=CONCATENATE("http://maps.google.co.uk/maps?f=d&source=s_d&saddr=",
SUBSTITUTE(B5," ","+"),"&daddr=",SUBSTITUTE(B6," ","+"))
 Press Enter to display the result.
 By using this formula, you can create a link to a Google Map that shows the route from Los Angeles to Pasco. The CONCATENATE function combines the cities into the link, and the SUBSTITUTE function ensures the city names are formatted correctly.
 How to Use the Link:
 Copy the generated link.
 Paste it into your browser’s search box.
 Explore the map to find out how far apart these two cities are from each other.
The CONCATENATE and SUBSTITUTE functions may also be used in Excel to determine the distance between two cities on the planet.
Method 4 – Creating a LAMBDA Function to Calculate the Distance Between Two Cities
The LAMBDA function in Excel allows us to build customized functions that can be used across a worksheet and accessed by familiar names. Once defined and given a name, these functions can be utilized anywhere in our workbook.
The Main Equation for Distance Calculation
The main equation for calculating the distance between two points on the Earth’s surface is as follows:
<code>D=acos(sinΦ1.sinΦ2+cosΦ1.cosΦ2cosΔλ).R
Where:
 Δλ indicates the difference between the longitudes (lon_2lon_1).
 Φ_{1 }and Φ_{2} sequentially indicate lat_1 and lat_2.
 R indicates the radius of the surface.
Creating the LAMBDA Function
Let’s create a LAMBDA function to calculate the distance between two cities by following these steps:
 Choose the cell where you want to display the result. In our example, we’ll select cell C8.
 Input the following LAMBDA function into the selected 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_2lon_1)))*r))(C5,D5,C6,D6)
 Press Enter to calculate the result.
 Customized Formula Using Name Manager:
 While the above formula works, it can be cumbersome to use repeatedly.
 To simplify, create a customized formula using the LAMBDA function:
 Go to the Formulas tab on the ribbon.
 Under the Defined Names group, click on Name Manager.


 In the Name Manager dialog box, click New.



 Name the formula (e.g., CityDistance).
 Enter the following formula in the Refers to field:

=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_2lon_1)))*r))

 Then, click OK.

 This will take you to the name Manager dialog again.
 Click on the Close button to complete the process.
 Now you can use the customized function CityDistance in your workbook.
 By applying the formula, we arrive at the distance of approximately 1358.524645.
Method 5 – Calculating the Distance Between Two Cities Using Excel VBA
In this method, we’ll create a userdefined function in VBA (Visual Basic for Applications) to determine the distance between two locations. We’ll make an API connection to retrieve data for a location using the Bing Map API key. Unfortunately, Google does not offer a free API for this purpose, so we’ll demonstrate using Bing Map.
Steps
 If you haven’t already, click here to create your own Bing Map API Key.
 Place the API key in cell C8 of your Excel workbook.
 Go to the Developer tab on the Excel ribbon.
 From the Code category, click on Visual Basic to open the Visual Basic Editor.
 Alternatively, press Alt + F11 to open the editor.
 You can also rightclick on your worksheet and choose View Code to access the editor.
 In the Visual Basic Editor, click on Module from the Insert dropdown menu.
 This will create a new module in your workbook.
 Copy and paste the following VBA code into the module:
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 "UserAgent", "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 entire workbook as a macroenabled workbook with the extension .xlsm.
 Now you can use the custom function CityDistance in your workbook.
 Input the latitude and longitude coordinates of the two cities as arguments to the function.
 The result will be the distance between the cities in kilometers.
VBA Code Explanation
 Function Name and Arguments:
 We start by defining our custom function with the name CityDistance.
 The function takes four arguments:
 lat1 (latitude of the first city)
 lon1 (longitude of the first city)
 lat2 (latitude of the second city)
 lon2 (longitude of the second city)
 Object and String Declarations:
 We declare SetupHTTP as an object and several string variables:
 InitialPoint
 EndingPoint
 DistanceUnit
 OutputUrl
 We declare SetupHTTP as an object and several string variables:
 Setting Initial Values:
 We set the DistanceUnit to kilometers (since we want the result in kilometers).
 The InitialPoint corresponds to the starting point of the URL link.
 Establishing API Connection:
 We set the parameters required to establish a connection between our VBA code and the Bing Map API.
 UserDefined Function:
 The core of our code is the userdefined function.
 We calculate the distance using the Haversine formula:
CityDistance=ACOS((SIN(RADIANS(lat1))*SIN(RADIANS(lat2)))+(COS(RADIANS(lat1))*COS(RADIANS(lat2))*COSRADIANS(lon2lon1))))*r

 Where:
 lat1 and lon1: These represent the latitude and longitude coordinates of the first point.
 lat2 and lon2: These represent the latitude and longitude coordinates of the second point.
 r: Represents the radius of the Earth (approximately 6,371 kilometers or 3,959 miles).
 SIN(RADIANS(lat1)) and SIN(RADIANS(lat2)): These terms calculate the sine of the latitudes of the two points after converting them from degrees to radians.
 COS(RADIANS(lat1)) and COS(RADIANS(lat2)): These terms calculate the cosine of the latitudes of the two points after converting them from degrees to radians.
 COS(RADIANS(lon2 – lon1)): This term calculates the cosine of the difference in longitudes between the two points after converting it from degrees to radians.
 ACOS(…): The entire expression inside the ACOS function calculates the dot product of the unit vectors corresponding to the two points’ coordinates.
 Finally, multiplying the result by r gives the actual distance between the two points.
 Distance Calculation: The result of this expression gives the distance (in the same units as the radius r) between the two points. If you want the distance in kilometers, use r = 6371 (Earth’s average radius in kilometers). For miles, use r = 3959 (Earth’s average radius in miles).
 Where:
 Save the entire workbook as a macroenabled workbook with the extension .xlsm.
 Now you can use the custom function CityDistance in your workbook.
 Input the latitude and longitude coordinates of the two cities as arguments to the function.
 The result will be the distance between the cities in kilometers.
Download Practice Workbook
You can download the practice workbook from here:
<< Go Back to Distance  Formula List  Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
Please, how do calculate Travel Time between two cities in Excel?
Hello, CRISTIAN!
Please check the article below, you will find the answer to your question.
https://www.exceldemy.com/calculatetraveltimebetweentwocitiesinexcel/
This is helpful, thank you!
What is the best way to calculate distances between multiple cities at the same time?
Let’s say I have 100 static locations & I want to see how close these locations are to 300 camp grounds for instance?
Hello Jake,
Thanks for sharing your query. Please find the attached Excel file with the solution. Let us know for more queries.
https://www.exceldemy.com/wpcontent/uploads/2023/04/CalculateDistanceBetweenTwoCities.xlsx
Thank you.
Regards,
Sanjida Mehrun Guria
Excel VBA & Content Developer
ExcelDemy
What is the Measuring unit of the result we got
Hello Kndn,
The Measuring unit of the result is “Mile”.