Distance between ZIP codes in Excel

To calculate the distance between ZIP codes in Excel, you need the latitude and longitude of each code and a formula that turns two coordinate pairs into a distance: the haversine formula. Excel has no ZIP code data, so the coordinates come from a lookup, and the formula does the rest. This tutorial builds that sheet step by step (Method 1), then shows how to pull ready-made distances from an API into Excel with Power Query or into Google Sheets with Apps Script (Method 2). The formulas below were calculated with the formulas Excel engine and checked against a Python implementation of the same math.

1. Get the coordinates of each ZIP code

Paste your ZIP codes into the free bulk ZIP code lookup (up to 300 codes, 182 countries) and download the CSV. It has the columns code, city, state, state_code, latitude and longitude.

Import it into a sheet named Coordinates with Data → From Text/CSV, and set the type of the code column to Text before you load it. Otherwise Excel turns 02108 (Boston) into 2108, and every lookup of that code fails.

The coordinates in this example are GeoNames’ values for these codes. Your download can differ in the last decimals, which moves the results by a fraction of a mile.

2. Look up the coordinates for each pair

Put the pairs you want to measure in a second sheet, Distances: the start ZIP code in column A and the destination in column B (formatted as text, like above). Columns C to F fetch the coordinates:

XLOOKUP needs Excel 2021 or Microsoft 365. In older versions, use INDEX and MATCH, which give the same result:

=INDEX(Coordinates!$E$2:$E$301,MATCH(A2,Coordinates!$A$2:$A$301,0))

Fill the formulas down for every row.

3. Calculate the distance with the haversine formula

In G2, for miles:

=2*3958.8*ASIN(SQRT(SIN(RADIANS(E2-C2)/2)^2+COS(RADIANS(C2))*COS(RADIANS(E2))*SIN(RADIANS(F2-D2)/2)^2))

For kilometres, replace 3958.8 (the Earth’s mean radius in miles) with 6371. The parts of the formula:

• RADIANS(E2-C2) and RADIANS(F2-D2): the difference in latitude and longitude, in radians, which Excel’s trigonometric functions expect.
• SIN(…/2)^2+COS(…)*COS(…)*SIN(…/2)^2: the haversine of the central angle between the two points.
• 2*radius*ASIN(SQRT(…)): turns that angle into a distance along the Earth’s surface.

With the coordinates above, the sheet returns:

The same formula with GeoNames’ coordinates for Anchorage (99501) and Beverly Hills gives 3,753.8 km; zipcodestack’s distance endpoint answers 3,754.89 km for that pair in its documentation, a difference of less than 0.05%.

You may also find a shorter formula built on ACOS (the spherical law of cosines). It gave the same distances to within 0.2 miles in our test, but it loses precision for points close together, so the haversine version is the safer choice.

How accurate is it?

• Straight line, not road distance. The result is the great-circle distance. Driving distances are longer.
• Centre points. A ZIP code covers an area, and its coordinates are a point within it. For two addresses in large rural ZIP codes the real distance can differ by miles.
• A round Earth. The formula treats the Earth as a sphere, which is accurate to within about 0.5%.

4. Get distances from the API instead

The formula method needs a coordinate table that you keep up to date. The zipcodestack distance API returns the distances directly, for 182 countries:

curl -G https://api.zipcodestack.com/v1/distance \
    -d code=99501 \
    -d compare=90210,15106 \
    -d country=us \
    -d unit=km \
    -H "apikey: YOUR-API-KEY"

{
    "query": {
        "code": "99501",
        "compare": ["90210", "15106"],
        "country": "us",
        "unit": "km"
    },
    "results": {
        "90210": 3754.89,
        "15106": 5115.42
    }
}

• compare takes up to 100 codes. unit is km (the default) or miles.
• Each distance in results counts as 1 request. Codes that are not found are left out of results and cost nothing.
• The free plan has 1,000 requests a month and 10 a minute, for testing and personal projects; commercial use starts with the Small plan. See pricing.

Excel: Power Query

1. Data → Get Data → From Other Sources → Blank Query.
2. In the Power Query editor, open Advanced Editor and replace the text with:

let
    Source = Json.Document(
        Web.Contents(
            "https://api.zipcodestack.com/v1/distance",
            [
                Query = [code = "10001", compare = "90210,60601,02108", country = "us", unit = "miles"],
                Headers = [apikey = "YOUR-API-KEY"]
            ]
        )
    ),
    Distances = Record.ToTable(Source[results]),
    Renamed = Table.RenameColumns(Distances, {{"Name", "To ZIP"}, {"Value", "Miles"}})
in
    Renamed

3. Click Done, then Close & Load. When Excel asks how to connect, choose Anonymous: the key is in the header.

The result is a table with one row per destination ZIP code. Data → Refresh All fetches the distances again. The key is saved in the workbook, so remove it before you share the file. The M code above passes Microsoft’s Power Query parser; we did not run it in Excel itself.

Google Sheets: Apps Script

1. Extensions → Apps Script, replace the code with the function below and save.
2. In the editor, open Project Settings → Script Properties and add the property ZIPCODESTACK_KEY with your API key, so the key is not in the code.

/**
 * Distance between postal codes, from the zipcodestack API.
 *
 * @param {string} from The postal code to measure from.
 * @param {string|Array} to One postal code, or a column of up to 100 codes.
 * @param {string} country Two-letter country code. Default "us".
 * @param {string} unit "miles" or "km". Default "miles".
 * @return The distance, or one distance per row; empty where a code was not found.
 * @customfunction
 */
function ZIPDISTANCE(from, to, country, unit) {
  country = String(country || 'us').toLowerCase();
  // Sheets turns 02108 into the number 2108: give US ZIP codes their leading zeros back.
  const code = (value) =>
    typeof value === 'number' && country === 'us' ? String(value).padStart(5, '0') : String(value).trim();

  const targets = (Array.isArray(to) ? to.flat() : [to]).map(code);
  const compare = [...new Set(targets.filter(Boolean))];
  if (compare.length > 100) throw new Error('At most 100 codes per call');

  const query = [
    'code=' + encodeURIComponent(code(from)),
    'compare=' + encodeURIComponent(compare.join(',')),
    'country=' + encodeURIComponent(country),
    'unit=' + encodeURIComponent(unit || 'miles'),
  ].join('&');

  const cache = CacheService.getScriptCache();
  const cacheKey = Utilities.base64Encode(Utilities.computeDigest(Utilities.DigestAlgorithm.MD5, query));
  let body = cache.get(cacheKey);
  if (body === null) {
    const response = UrlFetchApp.fetch('https://api.zipcodestack.com/v1/distance?' + query, {
      headers: { apikey: PropertiesService.getScriptProperties().getProperty('ZIPCODESTACK_KEY') },
      muteHttpExceptions: true,
    });
    body = response.getContentText();
    // Parameter errors come back with status 200 and no "results", e.g. {"compare": "required"}.
    if (response.getResponseCode() !== 200 || !JSON.parse(body).results) {
      throw new Error('zipcodestack ' + response.getResponseCode() + ': ' + body);
    }
    cache.put(cacheKey, body, 21600); // keep answers for 6 hours, the cache's maximum
  }

  const results = JSON.parse(body).results;
  const distance = (target) => (target in results ? results[target] : '');
  return Array.isArray(to) ? targets.map((target) => [distance(target)]) : distance(targets[0]);
}

Then, in any cell:

• =ZIPDISTANCE(A2, B2) for one pair, in miles.
• =ZIPDISTANCE("10001", B2:B50) for a whole column in one call. The results fill the rows next to it.
• =ZIPDISTANCE(A2, B2, "de", "km") for German postal codes in kilometres.

Prefer the range form for long lists: it sends one request to the API instead of one per cell, which keeps you under the free plan’s 10 requests a minute. Answers are cached for six hours, so recalculating the sheet does not use requests again. We ran this function in Node.js with stand-ins for the Apps Script services against a local copy of the endpoint; it has not been run inside Google Sheets.

5. Which method to use

Need every ZIP code within a distance instead of the distance between two? Use the ZIP code radius tool or the radius endpoint (10 requests per search).