# How to Calculate the Distance Between Two Cities in Excel (5 Methods)

## 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:

STEPS

• Select the Cells for City Names:
• Choose the cells where you want to input the city names. In our example, weâ€™ll select cells B5 and B6.
• Access the Data Tab:
• Go to the DataÂ tab on the Excel ribbon.
• Choose the Geography Data Type:
• Under the Data TypesÂ category, click on Geography.

• Find the Latitude:
• 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.

• Repeat for the Second City:
• Similarly, find the latitude for the second city (e.g., B6).
• Find the Longitude:
• 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.

• Repeat for the Second City:
• 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

• Select the Cell for Distance Calculation:
• Choose the cell where you want to display the distance. In our example, weâ€™ll select cell C8.
• Apply the Haversine Formula:
• Enter the following formula into the selected cell:

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

• Press Enter to see the result.

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

STEPS

• Select the Cell for Distance Calculation:
• Choose the cell where you want to display the distance (e.g., cell C8).
• Apply the Combined Formula:
• Enter the following 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`
• Press Enter to view the outcome.

• Result:
• 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.
• Apply the Formula:
• 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.

• 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:
• 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_2-lon_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:

• Select Cell C8:
• Choose the cell where you want to display the result. In our example, weâ€™ll select cell C8.
• Enter the Formula:
• 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_2-lon_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_2-lon_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 user-defined 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

• Create a Bing Map API Key:
• Place the API key in cell C8 of your Excel workbook.

• Access the Developer Tab:
• Go to the DeveloperÂ tab on the Excel ribbon.
• Open the Visual Basic Editor:
• 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 right-click on your worksheet and choose View CodeÂ to access the editor.

• Create a Module:
• In the Visual Basic Editor, click on ModuleÂ from the Insert drop-down menu.
• This will create a new module in your workbook.

• Copy and Paste the VBA Code:
• 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 "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 Workbook as .xlsm:
• Save the entire workbook as a macro-enabled workbook with the extensionÂ .xlsm.

• Using the CityDistance Function:
• 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
• 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.
• User-Defined Function:
• The core of our code is the user-defined function.
• We calculate the distance using the Haversine formula:

`CityDistance=ACOS((SIN(RADIANS(lat1))*SIN(RADIANS(lat2)))+(COS(RADIANS(lat1))*COS(RADIANS(lat2))*COSRADIANS(lon2-lon1))))*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).
• Saving the Workbook:
• Save the entire workbook as a macro-enabled workbook with the extensionÂ .xlsm.
• Using the CityDistance Function:
• 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.

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

Get FREE Advanced Excel Exercises with Solutions!
Sabrina Ayon

Sabrina Ayon, a Computer Science and Engineering graduate from United International University, has been an integral part of the ExcelDemy project for two years. She authored 150+ articles, excelling in instructing through visually engaging Excel tutorials. With a passion for teaching, Sabrina conducted sessions on Excel VBA, sharing her knowledge and insights with others. Currently holding the position of Project Manager for the ExcelDemy Visual Development Project, she oversees various aspects of the project, ensuring its smooth operation... Read Full Bio

1. Please, how do calculate Travel Time between two cities in Excel?

2. 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?

3. What is the Measuring unit of the result we got

Advanced Excel Exercises with Solutions PDF