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
What are Longitude and Latitude?
Convert text data to geographic data
Calculate Longitude and Latitude values
It would be wonderful if you provide a text copy of the formula in your blog post.
Hey George. I’ve edited the blog post and included the formulas
Where do you get the “3959” that you used on the formula?
Hi. I found it online whilst researching how to calculate the distance between 2 longitude/latitude points. It’s part of a formula called the Haversine formula and the constant 6371 is used to get distance in KM, while 3959 is used to get distance in miles.
I’m having issues with finding distances between some locations that are very far apart. London to Singapore, for instance, returns a #FIELD error in the Lat and Long values
Hi Adrian. Excel displays #FIELD because it is unable to locate any data for your request. In this case I think it is getting confused between Singapore the country and Singapore the city. Countries don’t have longitude/latitude values but cities do. I don’t think there is a way round this.