How to Calculate Distance Between Two Cities in Excel – Step by Step Guide

When working with location-based data, I often need to calculate the distance between cities in Excel. While Excel doesn’t have a built-in function for this, there are multiple methods to get the job done. In this guide, I’ll walk you through different ways to calculate the distance between two cities in Microsoft Excel.

Key Takeaways:

• Excel’s Geography Data Type helps retrieve latitude and longitude for cities.
• The Haversine formula calculates the shortest distance between two points using coordinates.
• Google Maps links can be generated in Excel to find distances visually.
• VBA can automate distance retrieval using the Google Maps API.
• Optimizing formulas and using shortcuts improves efficiency in large datasets.

 

Unlocking Excel for Distance Calculations

Preparing the Dataset

Before calculating the distance, I need to ensure that my dataset is well-structured. The key elements of my dataset are:

• City Names: Names of the cities for which I want to find distances.
• Latitude and Longitude: Geographic coordinates that Excel can fetch using the Geography Data Type.
• Distance Calculation Methods: Various approaches to computing the distance.

To start, I enter the names of cities in Column A:

Extracting Latitude and Longitude Using the Geography Data Type

Excel has a built-in Geography Data Type that allows me to retrieve location-specific details, including latitude and longitude.

STEP 1: Highlight the city names in the range A2:A5.

STEP 2: Go to the Data tab. Click on Geography in the Data Types group.

Excel converts the city names into geographical entities (indicated by a small map icon next to the city name).

STEP 3: Click on the small button and select Latitude as the field.

STEP 4: Next, select Longitude as a field.

STEP 5: Drag the formula down.

 

Methods to Calculate Distance Between Two Cities

Method 1: Applying the Haversine Formula

The Haversine formula calculates the shortest distance between two points on a sphere using latitude and longitude. This method is useful when you have the coordinates of the cities.

=6371 * ACOS(COS(RADIANS(90 – A2)) * COS(RADIANS(90 – A3)) + SIN(RADIANS(90 – A2)) * SIN(RADIANS(90 – A3)) * COS(RADIANS(B2 – B3)))

• 6371 is the Earth’s radius in kilometers.
• ACOS calculates the arc cosine.
• RADIANS converts degrees to radians.

This will provide the distance between the two cities in kilometers.

Method 2: Using a Google Maps Link

If you prefer a visual approach, you can generate a Google Maps link to check the distance. Use the CONCATENATE function to create a clickable Google Maps link:

=HYPERLINK(“https://www.google.com/maps/dir/” & A2 & “/” & A3, “Find Distance”)

Click the Link to open Google Maps with the route displayed.

Method 3: Using VBA

If you need an automated solution, VBA (Visual Basic for Applications) can fetch the distance from Google Maps API. Enable the Developer Tab in Excel if it’s not already enabled. Follow the steps belwo –

STEP 1: Open VBA Editor (ALT + F11) and insert a new module.

STEP 2: Paste the following VBA code:

Function GetDistance(origin As String, destination As String) As String
Dim http As Object
Dim JSON As Object
Dim URL As String
URL = "https://maps.googleapis.com/maps/api/distancematrix/json?origins=" & origin & "&destinations=" & destination & "&key=YOUR_API_KEY"
Set http = CreateObject("MSXML2.XMLHTTP")
http.Open "GET", URL, False
http.Send
Set JSON = JsonConverter.ParseJson(http.responseText)
GetDistance = JSON("rows")(1)("elements")(1)("distance")("text")
End Function

STEP 3: Replace ‘YOUR_API_KEY’ with your Google Maps API key.

STEP 4: Call the Function in Excel:

=GetDistance(“New York, USA”, “Los Angeles, USA”)

Press Enter to get the distance in miles or kilometers.

 

Smoothing the Journey: Tips and Techniques

Shortcuts to Speed Up Distance Calculation

To expedite distance calculations in Excel, one can adopt a host of shortcuts and techniques. The utilization of array formulas with Ctrl + Shift + Enter allows for efficient, simultaneous calculations across multiple sets of coordinates. Additionally, creating named ranges for frequently used constants or coordinate sets ensures that your work is not only faster but also more readable.

Embracing keyboard shortcuts for copying formulas down a column (Ctrl + D) or right across rows (Ctrl + R) saves significant time. It’s wise not to overlook the power of Excel tables; they offer dynamic ranges that automatically adjust when new data is added. The top benefits include reduced time spent on repetitive tasks, minimized human error, and a more organized workflow.

Optimizing Your Formulae for Faster Results

For crisper performance in Excel, optimizing your formulas is not just an option; it’s a necessity, especially when working with large datasets or complex distance calculations. I constantly remind myself to use simpler functions where possible, and to avoid volatile functions like INDIRECT and OFFSET, as they can increase calculation time significantly. Preferring SUMPRODUCT over array formulas can also yield quicker computations.

Furthermore, employing Excel’s conditional formatting sparingly can prevent unnecessary slowdowns, as can turning off automatic calculations when working on large spreadsheets (Options > Formulas > Workbook Calculations to “Manual”).

Improved calculation speed and efficiency, smoother spreadsheet operation, and better use of computational resources count among the benefits. However, balance is key—not all optimization techniques are suitable for every task, so it’s crucial to tailor your approach.

 

FAQs on Excel-Based Distance Calculation

How can I find latitude and longitude in Excel?

To find latitude and longitude in Excel, highlight city names, go to the Data tab, and select the Geography Data Type. Then, select Latitude and Longitude as fields to retrieve the geographic coordinates.

What is the most accurate method to calculate the distance between two citites?

The Haversine formula is the most accurate method for calculating distances between two cities using their latitude and longitude, as it accounts for the curvature of the Earth.

Can I use Google Maps in Excel?

You can use Google Maps in Excel by generating clickable links with the HYPERLINK function. This lets you create direct links to view distances between locations on Google Maps.

How do I automate distance calculations?

To automate distance calculations, you can use VBA (Visual Basic for Applications) with the Google Maps API to fetch distance data directly from the Maps service based on city names or addresses.

How can I speed up calculations?

To speed up calculations, utilize array formulas, use named ranges for frequently referenced data, and avoid volatile functions like INDIRECT and OFFSET that can slow down calculations in large spreadsheets.