Skip to content

Excel – measure distance between any points on Earth

If you want to measure a distance between any two points, there is an Excel formula that does just that. For that you solely need the coordinates of locations (Latitude, Longitude) and a blank Excel worksheet.

The formula for measuring the distance is as follows:

=6371 * ACOS(SIN([latitude of 1st location]*PI()/180)*SIN([latitude of 2nd location]*PI()/180) + COS([latitude of 1st location]*PI()/180) * COS([latitude of 2nd location]*PI()/180)*COS([longitude of 2nd location]* PI()/180-[longitude of 1st location] *PI()/180))

The formula is relatively long, but once executed, you get precise distance in kilometers. I will not go into explaining the details of the formula, but one thing I will call out: Note that the first number 6371 is Earth’s radius in kilometers. If you prefer to calculate in miles, then substitude the radius to 3959 miles.

Example

Lets say we want to calculate the distance to the famous Cologne Musical Dome Theater from few locations in Germany.

  • in Cells F4 and F5, we fill out Latitude and Longitude of the Dome Theater.
  • we make a table with multiple locations from which we want to calculate the distance to the Musical Dome.
  • Enter the long formula described above referencing to the latitude and longitude in the table
  • Voila! We have the list of distances to the Musical Dome.

I also made a video on this formula on my GanjingWorld channel here: https://www.ganjing.com/video/1fpvfjmvp344sy76a3omHlwjm1hq1c

References

Check out ACOS function here.

For those obsessed with the complexity of Geodesic distances and their calculations, check out this blog.

Leave a Reply

Your email address will not be published. Required fields are marked *