Excel - Calculate the Distance Between Two Locations and Show on a Map

In this video I show you how to calculate the distance between 2 cities anywhere in the world. My solution...

1. Converts the text-based city names to Geographical data types
2. Calculates the longitude and latitude values for the cities
3. Calculates the distance in miles between the 2 cities
4. Plots the cities on a map with a heading showing the city names and the distance

For full details see the timestamps below:

⏱ Timestamps
00:00:00 Introduction
00:01:51 What are Longitude and Latitude?
00:02:49 Convert text data to geographic data
00:03:33 Calculate Longitude and Latitude values
00:04:32 Calculate the distance between the cities
00:05:05 Create the map
00:07:41 Add location pins to the map
00:08:39 Create the heading by concatenating values

🗒 Notes and Resources

The formulas that I used in the demos:
=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"

✅ Want more FREE training?

🙋‍♂️ Let's connect on social

#excel #map #distance

[halliecampos]

I did not find this helpful for my purposes, but THANK YOU for being so transparent with your not wasting time. This was BRILLIANT, and I appreciate you!!

[excel_ladz]

This is excellent! Thanks so much lad 🔥

[ViniciusDalviTrancoso]

It helped me so much, thank you, professor

[idontknowexcel]

Just confirming, the map would be static. One could change the locations, but the map will always stay the same. Is that accurate?

[Awesome-Imalive]

Great video! I´m trying to figure out how to find the X, Y distance between 4 cities, using a city as reference (0, 0). Do you know how can I do this?

[il_han]

Very nice work, thank you. How is it calculated as driving between two distances?

[mario-t]

Thanks so much, was so easy, can it work with real street addresses let say in the same city?

[unohoncho]

I like it but there seems to be a degree of inaccuracy if you compare to the AA distance calculator - That doesn't mean the AA calculator is right of course (I don't even know how they calculate their distances) but I have to change the * 3959 value in the formula to 4700 to get it to somewhat compare (even still with a degree of inaccuracy when I've checked differing distances) - What is the * 3959 representative of?

[HanaJuhana-st]

Help me, how to make a distance matrix table use excel??

[abhim]

Does it give the shortest distance between Manchestor and London or distance by road? if i need to calculate distance by road from Manchesor and London, what is the formula ?

[arpitkumar]

Would it be possible to get the same done for list of countries, sites, address, Pin code and find which all are nearby each other and form a cluster

[robertreedholm]

My Excel does not have the geographic selection. how do I add that to excel?

[RichardBenjaminYrastorza]

I'm getting #num! error after I placed in the long and lat

[oliviahamelius]

Why did you use a factor of 3959 in the formula?

[danielledavidson]

How do you change to KM instead of miles