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 and Google Sheets, but a similar approach should also work in Numbers.

This guide assumes you're using Geocodio for your calculations and that latitude/longitude are in separate columns. Since Geocodio provides decimal degrees, all examples use decimal degrees. We've provided workbooks for Excel and Google Sheets with worked examples as a companion to this guide. To get the workbooks, enter your email above.

This guide walks 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 for one-to-one, one-to-many, and many-to-many relationships

Overview

This is the general process you'll need to follow for calculating distance in spreadsheets.

1. To find distance, first convert the addresses to coordinates

Upload your spreadsheet here. Geocodio will return decimal coordinates using WGS-84.

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 can 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.

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.

Distance Calculation Methods: Calculating one-to-one, one-to-many, and many-to-many relationships in Excel

These instructions use Geocodio's template workbooks for Excel and Google Sheets with worked examples of distance calculations, including formulas. To get the spreadsheets, enter your email in the form above and we'll send it to you.

One-to-One: Single Distance Between Two Points

This is the most basic distance calculation and is useful for finding the distance between two specific locations using coordinates from the same row. For example, you might need to find the shipping distance between two specific addresses, how far a customer lives from a particular store, or how far an employee lives from their work location.

Setup (Based on "One-to-One" Sheet)

• Location 1: Latitude in column A, Longitude in column B
• Location 2: Latitude in column D, Longitude in column E
• Results: Miles in column G, Kilometers in column H

Formula (Miles) - Cell G2

Output the distance in miles:

Formula (Kilometers) - Cell H2

Output the distance in kilometers:

One-to-Many: Find Closest/Furthest Location from a Single Point

This method finds the closest location from a single reference point to multiple destinations, using the structure from "Many-to-One or One-to-many" sheet. You can also use it to find the relationship between many points and one specific point. For example, you might have a GPS device in a vehicle that has broken down, and want to know which of your service centers are closest to the breakdown site.

Setup

• Reference Point: Single location coordinates (A2,B2)
• Destination List: Multiple locations with coordinates in "List B" sheet (columns E,F)
• Helper Column: Distance calculations in column G

Step 1: Create Distance Column (Cell G2)

Using miles:

Kilometers:

Where:

• $A$2, $B$2 = Reference point coordinates (locked with $ signs)
• 'List B'!E2, F2 = Each destination's coordinates

Step 2: Find Closest Location Name

This uses the street address as the name. You can use anything else for the name, such as a store number. (When you upload a file to Geocodio, we only add columns, so you can leave in any necessary references and they'll still be there when you download your file.)

Step 3: Get Closest Location

This is Cell K2 in the example.

Step 4: Find Furthest Location

This is Cell K3 in the example.

Many-to-Many: Find Closest Matches Between Two Lists

Given two lists, this method uses List A as the input and finds the closest location on List B. This example takes a location from "List A Geocodio Results" (Duluth GA, Los Angeles CA, etc.) and finds the closest location in "List B Geocodio Results" (Fort Worth TX, Gillette WY, etc.). This can be used for customer-to-store assignment, territory planning, and supply chain optimization between facilities.

Please note that this is a complex formula with significant processing overhead. For lists with more than 100 input/matching locations (ie., 100 inputs, 100 possible matches), consider using a database, as Excel or Google Sheets will likely freeze.

Setup (Create new "Results" sheet)

• List A: Locations from "List A" (coordinates in columns E,F)
• List B: Locations from "List B" (coordinates in columns E,F)
• Result Sheet: Summary with closest matches

Column Headers (Row 1)

• A1: "List A Location"
• B1: "Closest List B Location"
• C1: "Distance (Miles)"
• D1: "Distance (Kilometers)"

Google Sheets Formulas (Recommended)

Column A (List A Location Reference) - Cell A2: First, pull in the reference for the location you're starting from:

Column B (Closest List B Match) - Cell B2: Then scan all of List B to find the closest location:

Column C (Distance in Miles) - Cell C2: Output the distance in miles:

Column D (Distance in Kilometers) - Cell D2: Output the distance in kilometers:

Excel Alternative (Helper Column Method)

This alternative does not use array formulas, which are not supported by all versions of Excel. Because of that, Google Sheets is the recommended method.

In "List B Geocodio Results", add helper column G with this formula in G2:

Then use INDEX/MATCH to find minimum distance:

Copy Instructions

1. Select entire row 2 (A2:D2) in your results sheet
2. Copy down for all locations in "List A"
3. References automatically update: E2→E3, F2→F3, etc.
4. Each row shows one List A location matched with its closest List B location

Tips for Calculating Distance in Spreadsheets

1. Use Google Sheets: for complex array formulas, as it handles them more reliably than Excel
2. Consider data limits: Very large datasets (10,000+ comparisons, or 100x100 in a many-to-many) may require database solutions. For example, if you're doing a many-to-many relationship between 100 locations on each list (100x100), you shouldn't use Excel or Google Sheets.
3. Validate coordinates: Ensure latitude/longitude are numeric, not text
4. Test with small samples: Before applying to the full dataset, test with one cell first
5. Remember your cell references: Use locked cells ($E$1) for fixed reference points in one-to-many calculations

Sources

We are 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.