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:
- Enter the names of the two cities into two cells
- Convert the text-based city names to Geographical data types
- Calculate the longitude and latitude values for the cities
- Use a formula to calculate the distance in miles between the cities
- Create a map using the built in mapping tool
- 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