Calculate Distance Between Two Addresses or Coordinates in a Spreadsheet

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.