How to Calculate the Distance Between Two Cities in Excel (5 Methods) - ExcelDemy (2024)

Dataset Introduction

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 Datatab on the Excel ribbon.
• Choose the Geography Data Type:
  • Under the Data Typescategory, click on Geography.

• Find the Latitude:
  • Select the cell where you want to display the latitude (e.g., B5).
  • Type the following formula:

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

• • 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).
• Dataset Ready:
  • 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:

• • Press Enter to see the result.

• Result:
  • By using the formula, we obtain the result1367.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:

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

• • Press Enter to view the outcome.

• Result:
  • By applying the formula, we arrive at the distance of approximately1357.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:

• Select the Cell for Google Map Link:
  • 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:

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

Where:

• Δλ indicates the difference between the longitudes (lon_2-lon_1).
• Φ₁ and Φ₂ 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:

• • 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 Formulastab 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 tofield:

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

• Access the Developer Tab:
  • Go to the Developertab on the Excel ribbon.
• Open the Visual Basic Editor:
  • From the Code category, click on Visual Basicto open the Visual Basic Editor.
  • Alternatively, pressAlt + F11to open the editor.

• • You can also right-click on your worksheet and choose View Codeto access the editor.

• Create a Module:
  • In the Visual Basic Editor, click on Modulefrom 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 ExplicitPublic 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 StringInitial_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_UnitSetup_HTTP.Open "GET", Output_Url, FalseSetup_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 CityDistancein 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.

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