How to Geocode in Excel: Address Mapping and Distance Formulas

Working out how to geocode in Excel usually means hitting a wall. Standard spreadsheet setups do not have built-in formulas to convert text addresses into latitude and longitude coordinates. Users typically have to export their data, use a web-based geocoding tool, and import the results back into Excel, completely breaking their workflow. While the mapping features in Excel allow for basic geographic visualisations, calculating the distance between two specific addresses requires complex mathematical formulas based on pre-existing coordinates, not raw text.

Developers and data analysts often solve this by writing custom VBA scripts or configuring Power Query to connect to external mapping APIs like Google Maps or OpenStreetMap. However, these methods require maintaining API keys, handling authentication, and managing usage limits. If you want to avoid scripting, Google Sheets provides a straightforward alternative. By moving your data to a spreadsheet equipped with a dedicated add-on like InstaMaps, you can run location analysis using simple, standard formulas. This guide breaks down the standard Excel approaches and shows how to bridge the gap using formula-based geocoding.

The Standard Options for Geocoding in Excel

Excel does not feature a native =GEOCODE() function out of the box. To convert a list of addresses into coordinates, users traditionally rely on a few different workarounds. The most common method involves using Power Query to call an external API. You set up a web request within the query editor, pass your address column to the API endpoint, and parse the JSON response to extract the latitude and longitude. This process requires a solid understanding of data transformations and web requests.

Another method involves writing custom VBA macros. A VBA script can loop through a column of addresses, send an HTTP request to a geocoding service, and write the coordinates into the adjacent cells. Both of these Excel geocoding methods require programming knowledge and a reliable third-party API key. You must also account for API rate limits, which can halt your data processing if you are working with large datasets. Excel's built-in Map Chart feature is helpful for visualising data by country or region, but it cannot plot individual street addresses or calculate routes without this external data processing.

1. Power Query: Connects to external APIs but requires managing web requests and JSON parsing.

2. VBA Macros: Automates HTTP requests but requires programming knowledge to maintain.

3. Third-party plugins: Often require paid subscriptions for bulk address processing.

Calculating Excel Distance Between Two Addresses

Finding the distance between two locations in Excel is another common challenge. If you already have latitude and longitude coordinates, you can use the Haversine formula. This mathematical equation calculates the shortest distance over the earth's surface, giving you the 'as the crow flies' measurement. However, setting up the Haversine formula requires precise mathematical inputs and does not account for actual roads, traffic conditions, or driving routes.

To calculate the actual driving distance or time between two addresses in Excel, you again need to rely on external APIs or paid plugins that interface with mapping services. Setting this up in Excel means managing API pricing structures and strict usage limits. For teams that simply need to measure travel metrics for a sales route or delivery radius, maintaining these complex integrations often proves too tedious for the value they provide.

1. Straight-line distance: Use the Haversine formula if you already have coordinates.

2. Driving distance: Requires connecting to external routing APIs via VBA or Power Query.

The One-Formula Google Sheets Alternative

If you do not want to maintain API keys or write VBA scripts, moving your data to Google Sheets offers a much faster path to location analysis. The InstaMaps add-on for Google Sheets provides custom functions that operate exactly like native spreadsheet formulas. You do not need to write code, manage authentication, or parse JSON responses. The add-on handles the API requests securely in the background.

To geocode a list of addresses, you simply use the =GEOCODE() function. If you have a list of addresses in column A, typing =GEOCODE(A2:A50) will return the exact coordinates for that entire range. You can access these formulas without typing by opening the sidebar at Extensions > InstaMaps > Formulas. InstaMaps is free to use and provides a generous baseline of 100 lookups per day, which increases to 1,000 lookups per day with a simple free email unlock.

1. Use =GEOCODE(A2:A50) to get coordinates for a column of addresses.

2. Use =CLEAN_ADDRESS(A2) to standardise messy user inputs before processing.

3. Access the formula sidebar via Extensions > InstaMaps > Formulas.

Measuring Real Travel Metrics with Formulas

Once you have your data in Google Sheets, calculating distances and travel times becomes just as straightforward. Instead of manually configuring API calls or dealing with complex Haversine mathematics, you can use InstaMaps formulas to return real-world driving metrics. If you want to find the driving distance between a warehouse in cell A2 and a customer location in cell B2, you use =DISTANCE(A2, B2).

You can also calculate the time it takes to travel between two points using =TRAVEL_TIME(A2, B2). These formulas pull accurate routing data directly from Google Maps, giving you metrics that reflect actual driving conditions rather than just straight-line distance. If your source data contains typos or formatting errors, running =CLEAN_ADDRESS(A2) first ensures your distance calculations are completely accurate.

1. Use =DISTANCE(A2, B2) for accurate driving distances in miles or kilometres.

2. Use =TRAVEL_TIME(A2, B2) to estimate drive time based on current routing data.

Generating Shareable Maps from Your Spreadsheet

Plotting addresses visually is often the end goal for location data. While Excel has mapping features, sharing them outside of your organisation or embedding them on a website can be difficult. With Google Sheets and InstaMaps, you can generate a live map using a single formula. Typing =INSTAMAP(A2:A50) creates a hosted map URL based on your selected data.

This map updates automatically whenever you change a value in your spreadsheet. You can share the link with colleagues or clients, giving them a live view of your data without needing to grant them access to the underlying spreadsheet. To view our pre-built formatting structures, you can download templates at get-instamaps.com/templates. Furthermore, you can use =ROUTE_LINK(A2:A12) to generate a Google Maps navigation link, which uses Google Maps' official URL scheme and is perfect for planning a driving route with a maximum of 11 stops.

1. Generate a live map using =INSTAMAP(A2:A50).

2. Create a navigation link using =ROUTE_LINK(A2:A12), supporting up to 11 stops.

3. Download pre-made structures at get-instamaps.com/templates.

Common Questions