Function Problems

How to Calculate Distance Between Street Addresses in Excel

Steve K Oct 12, 2026 871 views

Question details

The user needs to calculate driving distances between a home address and approximately 1,000 customer addresses, and dynamically highlight locations that are more than 140 miles away.

How to Calculate Distance Between Street Addresses in Excel

Product: Microsoft Excel
Device & OS: not provided
Scenario: Managing a large customer address dataset and identifying locations outside a specific geographical radius.
Observed behavior: Excel does not have a native formula or built-in function to calculate real-world driving distances directly from street addresses.

Before you start

Since spreadsheet software cannot natively calculate geographical driving routes, ensure your addresses are properly formatted in standard columns (e.g., Street, City, State, Zip) so they can be easily processed by a mapping service or API.

Solution 1Recommended

Use an External Mapping Tool and Conditional Formatting

Calculate the distances using a third-party mapping service or distance API, import the results back into Excel, and use Conditional Formatting to highlight rows exceeding 140 miles.

Because Excel does not feature a built-in routing engine, you must rely on external resources like the Google Maps API, Bing Maps API, or a batch distance calculator online to find the exact mileage between addresses.

Once you have generated the distance values and imported them back into your spreadsheet, you can apply Excel's Conditional Formatting feature to automatically highlight any distance greater than your target limit.

Calculate Distances Externally

Export your list of addresses and process them through a web-based batch distance calculator or a custom API script to determine the mileage from the home address.

Add Distance Data to Excel

Create a new column next to your customer addresses (e.g., Column C named 'Distance in Miles') and paste the calculated results into this column.

Select the Distance Range

Highlight all the cells containing your new distance data that you want to format.

Access Conditional Formatting

Navigate to the 'Home' tab on the Excel ribbon, click on 'Conditional Formatting', hover over 'Highlight Cells Rules', and select 'Greater Than...'.

Apply the Highlighting Rule

In the dialog box, enter '140' into the value field, choose a formatting style (such as Light Red Fill with Dark Red Text), and click 'OK' to highlight the distant locations.

Use an External Mapping Tool and Conditional Formatting

Alternative for Advanced Users: If you are familiar with coding, you can write a custom VBA macro in Excel that uses XMLHTTP to query the Google Maps Distance Matrix API directly and populate the cells automatically.

Manage Address Data Efficiently

Highlight Address Distances Easily with WPS Spreadsheet

After calculating your distances with an external mapping tool, you can easily organize your customer dataset and highlight specific ranges using WPS Spreadsheet. It offers full compatibility with Excel files and a lightweight, intuitive interface for advanced data formatting.

1. Open Your Data File: Launch WPS Office and open your spreadsheet containing the customer addresses and the imported distance values.
2. Select the Target Column: Click and drag to highlight the specific column containing your distance data in miles.
3. Open Conditional Formatting: Go to the 'Home' tab, click the 'Conditional Formatting' icon, and choose 'Highlight Cells Rules' followed by 'Greater Than'.
4. Set the Distance Condition: Type 140 in the value box, pick your preferred highlight color from the drop-down menu, and click 'OK' to instantly visualize your results.

Fully compatible with Microsoft Excel formats (.xlsx, .xls, .csv)Built-in, powerful Conditional Formatting featuresLightweight and smooth performance even with thousands of address rowsFree to download with a familiar user interface

microsoft office alternative - wps office

Frequently Asked Questions

Can Excel automatically calculate driving distance between two addresses?

No natively. Excel requires a connection to an external mapping service (like Google Maps or Bing Maps) via a VBA script or an add-in to calculate driving routes and distances.

How do I calculate the distance between zip codes in Excel?

While straight-line (as the crow flies) distance can be calculated using latitude and longitude coordinates with the Haversine formula, driving distances between zip codes still require a third-party API or mapping tool.

How do I highlight an entire row based on the distance value?

Select your entire data table, go to Conditional Formatting, and choose 'New Rule'. Select 'Use a formula to determine which cells to format'. Enter a formula like =$C2>140 (assuming distance is in column C) and choose a fill color. The dollar sign locks the column so the entire row gets highlighted.

Excel & Data Problems