In this video I show you how to calculate the distance between 2 cities anywhere in the world and then create a map that displays the locations and the distance between them. This is all done with Excel. No 3rd Party apps or services needed!

To do this:

  1. Enter the names of the two cities into two cells
  2. Convert the text-based city names to Geographical data types
  3. Calculate the longitude and latitude values for the cities
  4. Use a formula to calculate the distance in miles between the cities
  5. Create a map using the built in mapping tool
  6. Add a text box to the map that shows the city names and the distance

The formulas that I used:

=INT(ACOS(COS(RADIANS(90-B4)) * COS(RADIANS(90-B5)) + SIN(RADIANS(90-B4)) * SIN(RADIANS(90-B5)) * COS(RADIANS(C4-C5))) * 3959)
=VALUETOTEXT(A4) & " - " & VALUETOTEXT(A5) & ": " & CHAR(10) & B7 & " Miles"

Note that this solution uses Excel’s 3D Map tool and Geographic Data Types which aren’t available in all versions of Excel


What are Longitude and Latitude?

Convert text data to geographic data

Calculate Longitude and Latitude values

Calculate the distance between the cities

Create the map

Add location pins to the map

Create the heading by concatenating values