How to calculate the distance between points in a spreadsheet in Excel, Google Sheets, or Numbers. Figure out which address point is closest to another address point or location.
This guide walks you through how to calculate the distance between two points in a spreadsheet using the haversine distance formula, which is also called straight-line or as-the-crow-flies distance. (It is not driving distance.)
We're using Excel here, but the same functions will also work in Google Sheets or Numbers.
This guide walk you through how to:
- Find the distance between two sets of GPS latitude/longitude coordinates (or addresses, once you've converted them to coordinates)
- How to find the distance in a many-to-one relationship, such as finding the closest store to a distribution center
You can see worked examples, including formulas, in this downloadable Excel example.
1. To find distance, first convert the addresses to coordinates
Upload your spreadsheet here. We will return decimal coordinates.
2. To find the distance between points, put them in a spreadsheet
Let's say you have a distribution center and you want to know approximately how far away each of your retail stores are.
Depending on the number of locations you have, you may put them in one spreadsheet. If you have a lot of locations, you might have multiple sheets within the same workbook. Our example provides templates for both. Download the example here.
3. Use the haversine distance formula to find the distance
You will need to choose whether you want miles or kilometers. We have provided examples for both.
Sources
We indebted to the formulas written here.
You can read more about the benefits and limitations of the haversine formula and a discussion of other formulas here.
You can use this distance calculator to find out the distance between two or more points anywhere on the earth. In other words, the distance between A and B. Click once on the map to place the first marker and then click again to position the second marker. The distance between the points will then be displayed. You can also build up a series of locations to find a total distance.
[Map Height : Small - Medium - Large]
Use search result as a distance marker? Yes No
...Loading...
Upload Coordinates
Please paste in the list of coordinates to add to your route in the text box below:
Generate Link For This Route
Share your route on Facebook, Twitter or by email
Please copy the link from this text box:
About
An important feature of this distance calculator tool is that it is "as the crow flies", so traveling in real life will normally involve larger distances, but this may also help those who need to measure off-road distances.
Want to save your route? Try the Advanced Google Maps Distance Calculator
How To Use
- Zoom and drag the map to find the location of marker 1
- Click to place marker 1.
- Zoom and drag the map to find the location of marker 2.
- Click to place marker 2.
- The distance will be calculated then displayed below the map in miles or kilometers. You can change between miles or km at any time
- Add as many points as required and the distance will build up.
- You can also drag and drop markers after they have been put on the map
- To share your route and distance calculation use the option [Generate Link For This Route]. You can then copy the link generated and send this via Twitter or Facebook.
Note
All distances are estimations so Daft Logic does not accept any responsibility that may arise when the value reported is incorrect.
You can move the markers after they have been positioned so you can modify you route on the fly and see how the distance will be affected.
Options
- Click [Clear Last] to undo the last marker placed
- Click [Zoom To Fit] to zoom the map around all markers
- Click [Clear Map] to erase all markers and start again
- Use the Quick Find text box to quickly relocate the map
- You can choose between miles and kilometers (km) for the Total Distance readout
- Click [Show Elevation] to get a chart showing the elevation of your route
How it Works
Clearly you, the user inputs two 'points' that are used to calculate the distance. Calculating the "As the Crow Flies" distance is a matter of using Great Circle formula. Then the main problem is converting the Google Map unit to miles and kilometers.
The map will also automatically save your route in your temporary internet cookies so that you can return and see the route as you last left it. If you ever need to start from scratch then use the [Clear Map] button.
Benchmark Tests
Map showing the Mary Peters Track in South Belfast. The reading shows 0.394km which is clearly a reasonable measurement for an athletics track.
Map showing the estimated distance between New York and Paris. All references that can be found suggest that this is an accurate measurement.
Map showing the Seven Mile Straight in County Antrim, Northern Ireland. This shows a reading of 6.910 miles.
Map showing the distance between Belfast and Dublin. As far as I can see this is an accurate estimate (if there is such a thing). This trip would be approximately 100 miles by road so the proverbial crow would have less distance to travel.
Relevant Links
Leaflet Maps
Further Uses and Ideas
- Remember auto-pan setting between sessions
- Show difference in elevation between markers
- Export a list of all waypoints and show the distance between each
Version History
1.0 | 29/12/2005 | The Beginning |
2.0 | 19/01/2006 | Stop post back between clicks Calculate Mid Point Zoom to fit feature More than one marker as running total |
3.0 | 15/06/2007 | Move to API v2 Great Circle display and distance calculation Option to resize map height |
3.1 | 25/09/2007 | Added print map option |
3.2 | 01/10/2007 | Added Quick Find |
4.0 | 20/01/2008 | Added Scroll Wheel Zoom in and out Complete conversion to API v2 Better Zoom to Fit Functionality Added crosshairs curser |
4.1 | 21/02/2008 | Removed Scroll Wheel Zoom in and out Added marker drag and drop functionality Removed Print Option (Ctrl+Print Screen works better) |
4.2 | 10/06/2008 | Added Nautical Miles option |
4.3 | 20/10/2008 | Added drop down map type option |
4.4 | 17/12/2008 | Map saves last position for when you next visit |
4.5 | 04/01/2009 | Fixed autopan issue when map is resized |
4.6 | 28/09/2009 | Removed Quick Find Added Google Search bar |
4.7 | 06/10/2009 | Brought Back Quick Find! Removed Google Search bar! Added Route Autosave via cookies Added Marker tooltip showing marker id and lat/lng Added Feet measurement Added Metres measurement |
4.8 | 08/10/2009 | Google Search can now be enabled by a check box More settings are now saved such as toggle markers, Google Search and Map Type |
5.0 | 31/07/2010 | Implemented Google Maps Javascript API V3 Google Search bar at bottom left is not yet available. sorry! Removed show mid point button Uses new cookie so any previous systems/settings on your machine will be erased! sorry! Please report any issues or bugs! |
5.1 | 02/08/2010 | Fixed autopan issue when map is resized |
5.2 | 09/08/2010 | Updated Quick Find. Now places marker at location |
5.3 | 31/07/2010 | Updated Quick Find. Now adds a distance marker upon finding a result unless the user opt's out of this Updated Quick Find. Now allows enter/return to be hit to cause the search. No need to click the Search button Updated Quick Find. Now pre-selects text after a search is complete to allow rapid input of multiple locations Updated Quick Find. If you perform an unattached search (not a distance marker), the search result marker can be easily removed from the map by clicking on that marker |
5.4 | 05/02/2012 | Updated Quick Find. Now zooms in to find the location |
5.5 | 14/02/2012 | Fixed issue where a marker that had been repositioned retained its original lat/lng in the text that appears during hover over |
5.6 | 15/02/2012 | Added new upload coordinates function |
5.7 | 10/11/2013 | Removed Google Local Search API (Deprecated) |
5.8 | 22/01/2014 | New option added to allow you to share your distance calculator route on Twitter, Facebook or email |
5.9 | 25/01/2014 | New option added to show the elevation of your route on a bar chart |
5.10 | 18/11/2015 | Implemented a mouseover event so when you hover over the elevation chart, a marker on the map highlights the location |
5.11 | 30/12/2015 | Its now possible add a marker to the route midway along the route. You can also delete a marker by right clicking on the dot below the marker |
5.12 | 04/04/2016 | Updates to print formatting |
5.13 | 09/06/2016 | Added yards units of distance measurement |
5.14 | 10/01/2017 | The marker will display the distance up to that leg on the route when you hover over it |
5.15 | 23/02/2017 | New option to upload GPX files |
5.16 | 21/09/2017 | Elevation units changed to feet |
5.17 | 21/02/2018 | Location displayed when you hover over a marker now has fewer decimal places |
5.18 | 11/08/2018 | Google Maps search removed due to costs. Replaced with custom search tool |
6.0 | 1st February 2021 | Implemented Leaflet Maps |
6.1 | 5th February 2021 | Bug Fix : Clicking on an existing marker to add to the route did not work and also caused the tool to stop functioning Bug Fix : Autopan did not work initially until swicthed off and on. |
6.2 | 12th February 2021 | New option : Edit Squares can be togged On/Off |
6.3 | 3rd March 2021 | Edit Squares are Off when the map first loads but can still be toggled on |
6.4 | 15th March 2021 | Lines are displayed as Geodesic when edit mode is off |
6.5 | 22nd March 2021 | Small bug fix |
6.6 | 26th May 2021 | Bug fix : Upload Coordinates should now work. Bug fix : Tiny URL links should now work |
6.7 | 25th April 2022 | Bug fix : Upload Coordinates |