Geocoding Addresses in Excel: A 2026 Guide

Geocoding is the process of turning a street address into latitude and longitude coordinates, so you can plot customers on a map, measure how far people travel to reach your store, or join your data to anything else that knows about geography.

We first published a version of this post years ago, built around a macro-enabled spreadsheet you downloaded from us. That approach has aged badly — the download is long gone, two of the three services it called no longer exist, and macro-enabled workbooks are now blocked by default in most organisations. This is the version we'd actually recommend today.

There are two good approaches, and which one you want depends entirely on how many addresses you have.

If you have New Zealand addresses: don't pay per lookup

This is the part most guides miss. Toitū Te Whenua LINZ publishes the NZ Addresses dataset free through the LINZ Data Service. It contains every allocated address in the country as advised by territorial authorities, with coordinates, and it's updated weekly.

For bulk New Zealand geocoding, downloading that as CSV and doing a local lookup beats calling a paid API on every count: no per-request cost, no rate limits, no sending your customer list to a third party, and it's the authoritative source rather than a best guess.

The workflow is straightforward:

1. Download the NZ Addresses layer from the LINZ Data Service as CSV.
2. Load it into Power Query in your workbook (Data → Get Data → From File → From Text/CSV).
3. Normalise both address columns — uppercase, strip punctuation, collapse double spaces — into a matching key.
4. Merge your address list against it (Home → Merge Queries, left outer join).

The catch is that real-world addresses are messy — "1 Queen St" versus "1 Queen Street", unit numbers written five different ways — so an exact join will typically match somewhere between 70% and 90% of a hand-entered list. Run the exact join first, then send only the unmatched remainder to an API. On a list of 10,000 addresses that turns a 10,000-call bill into a 1,500-call one.

For everything else: Power Query and a geocoding API

For international addresses, or small New Zealand lists where the setup isn't worth it, call a geocoding API directly from Power Query. No macros, and it refreshes like any other query.

Open Data → Get Data → Launch Power Query Editor, then New Source → Blank Query, open the Advanced Editor, and paste:

let
    Geocode = (address as text) as nullable record =>
        let
            ApiKey = "YOUR_API_KEY",
            Response = Json.Document(
                Web.Contents(
                    "https://maps.googleapis.com/maps/api/geocode/json",
                    [Query = [address = address, key = ApiKey, region = "nz"]]
                )
            ),
            Location =
                if Response[status] = "OK" then
                    [
                        lat = Response[results]{0}[geometry][location][lat],
                        lng = Response[results]{0}[geometry][location][lng],
                        matched = Response[results]{0}[formatted_address]
                    ]
                else
                    [lat = null, lng = null, matched = Response[status]]
        in
            Location
in
    Geocode

Name the query Geocode. Then in the query holding your addresses, add a custom column with Geocode([Address]) and expand it into lat, lng and matched columns.

Three details in there matter more than they look:

• [Query = [...]] instead of string concatenation. This is the important one. If you build the URL by joining text together, Power Query treats it as a dynamic data source and refuses to refresh it on a schedule. Passing parameters through the Query record keeps the base URL static, so scheduled refresh keeps working.
• region = "nz" biases ambiguous results toward New Zealand. Without it, "Wellington" has a decent chance of landing in Somerset.
• Returning matched gives you the address the API thought you meant. Always eyeball this column — silent mismatches are the main way geocoding goes wrong, and they're invisible if you only return coordinates.

On API keys and cost

Google's Geocoding API needs a Google Cloud project with billing enabled, and charges per request beyond a monthly free allowance. Azure Maps is a reasonable alternative with comparable quality. OpenStreetMap's Nominatim is free but its usage policy prohibits bulk geocoding — fine for a handful of lookups, not for your customer list.

Whichever you use, don't paste the key into a workbook you're going to email around. Excel offers a Web API credential type when Power Query first prompts for authentication — use that, and the key stays in your local credential store rather than in the file.

Getting a sensible result

A few things worth knowing before you trust the output:

• Check the match quality, not just the coordinates. An address that fails to match often still returns a result — the centre of the suburb, or the city. Coordinates that all cluster on one point are the tell.
• Rate limits are real. Adding a small delay between calls avoids tripping them on larger lists. If a refresh is taking a long time, it's working.
• Geocode once, store the result. Coordinates for a given address don't change. Cache them in a column rather than re-calling the API on every refresh.
• Think about privacy before you send the list anywhere. Customer addresses are personal information under the Privacy Act. That's the strongest practical argument for the LINZ approach on New Zealand data: nothing leaves your machine.

Then plot it

Once you have coordinates, Excel's built-in 3D Maps (Insert → 3D Map) will plot them directly, which is enough for a quick look. For anything you intend to share, Power BI's map visuals handle scale better and let you layer in the rest of your reporting.

The interesting work usually starts after the plotting — catchment analysis, drive-time isochrones, joining to census meshblocks for demographics. If you get to that point and want a hand, Power BI is where we usually take it next.