🌍 Download Free Geocoding Templates (Excel + Google Sheets)

📥 Download the Templates

🟢 Excel Geocoding Template (.xlsx)

⚠️ First Time?

This starter workbook works in any recent Excel. For the first time only, you’ll import the VBA macro, then you can use it normally.

👉

Download Excel Template (.xlsx)

(needs VBA macro)

✅ First-time setup (one-time)

1. Open Geocoding_Template.xlsx and save it as .xlsm: File → Save As → Excel Macro-Enabled Workbook (*.xlsm).
2. Press ALT + F11 to open the VBA editor → Insert → Module.
3. Paste the VBA from the “📜 VBA Macro Code” section below into the new module.
4. (Optional but recommended) Install the free VBA-JSON parser:
   • Download from github.com/VBA-tools/VBA-JSON.
   • In VBA editor: File → Import File…, choose JsonConverter.bas.
   • In Tools → References…, tick “Microsoft Scripting Runtime” if available.
5. Back in Excel, enable macros if prompted: File → Options → Trust Center → Trust Center Settings → Macro Settings.
6. Enter Latitude in column A and Longitude in column B.
7. Run the macro: Developer → Macros → GetAddresses → Run.
8. Keep at least 1 second between lookups (already handled in the sample macro) to respect Nominatim limits.

📜 VBA Macro Code (Reverse Geocoding)

Option Explicit

Sub GetAddresses()
    Dim http As Object
    Dim lat As Variant, lon As Variant, url As String, json As String
    Dim i As Long, lastRow As Long

    Application.ScreenUpdating = False

    lastRow = Cells(Rows.Count, 1).End(xlUp).Row
    For i = 2 To lastRow
        lat = Cells(i, 1).Value
        lon = Cells(i, 2).Value

        If Len(lat) > 0 And Len(lon) > 0 Then
            url = "https://nominatim.openstreetmap.org/reverse?format=json&lat=" & lat & "&lon=" & lon & "&zoom=18&addressdetails=1"

            Set http = CreateObject("MSXML2.XMLHTTP")
            http.Open "GET", url, False
            http.setRequestHeader "User-Agent", "ExcelGeocoder - basicexceltutorial.com"
            http.Send

            If http.Status = 200 Then
                json = http.responseText
                Cells(i, 3).Value = ExtractJsonValue(json, "display_name")
            Else
                Cells(i, 3).Value = "HTTP " & http.Status
            End If

            Application.Wait Now + TimeValue("0:00:01")
        End If
    Next i

    Application.ScreenUpdating = True
End Sub

Function ExtractJsonValue(ByVal json As String, ByVal key As String) As String
    Dim token As String, startPos As Long, valueStart As Long, valueEnd As Long, valText As String

    token = """" & key & """:"""
    startPos = InStr(1, json, token, vbTextCompare)
    If startPos = 0 Then
        ExtractJsonValue = "Not found"
        Exit Function
    End If

    valueStart = startPos + Len(token)
    valueEnd = InStr(valueStart, json, """")

    Do While valueEnd > 0 And Mid$(json, valueEnd - 1, 1) = "\"
        valueEnd = InStr(valueEnd + 1, json, """")
    Loop

    If valueEnd > valueStart Then
        valText = Mid$(json, valueStart, valueEnd - valueStart)
        valText = Replace(valText, "\/", "/")
        valText = Replace(valText, "\\", "\")
        ExtractJsonValue = valText
    Else
        ExtractJsonValue = "Not found"
    End If
End Function

🔁 Next time you use it

• Open your saved .xlsm workbook, enable macros, paste new coordinates, and run GetAddresses.

---

🟡 Google Sheets Template

Use this online template with Google Apps Script. No installation needed.

👉 Open Google Sheets Template – Copy of Geocoding (click to make a copy)

✅ First-time setup (one-time)

1. Open the link and click Make a copy.
2. Go to Extensions → Apps Script, confirm the GetAddresses function exists (paste the script below if needed), then click Run.
3. Complete the authorization prompts (Google will show the required scopes).

📜 Google Apps Script Code (Reverse Geocoding)

function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('Geocoding')
    .addItem('Get Addresses', 'GetAddresses')
    .addToUi();
}

function GetAddresses() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const startRow = 2;
  const lastRow = sheet.getLastRow();
  if (lastRow < startRow) return;

  const data = sheet.getRange(startRow, 1, lastRow - startRow + 1, 2).getValues();
  for (let i = 0; i < data.length; i++) {
    const lat = data[i][0];
    const lon = data[i][1];

    if (lat !== '' && lon !== '') {
      try {
        const url = `https://nominatim.openstreetmap.org/reverse?format=json&lat=${encodeURIComponent(lat)}&lon=${encodeURIComponent(lon)}&zoom=18&addressdetails=1`;
        const response = UrlFetchApp.fetch(url, {
          headers: { 'User-Agent': 'GoogleSheetsGeocoder - basicexceltutorial.com' },
          muteHttpExceptions: true
        });
        const result = JSON.parse(response.getContentText());
        const display = (result && result.display_name) ? result.display_name : 'Address not found';
        sheet.getRange(i + startRow, 3).setValue(display);
        Utilities.sleep(1000);
      } catch (e) {
        sheet.getRange(i + startRow, 3).setValue('Error: ' + e.message);
      }
    }
  }
}

▶️ How to use

1. Enter Latitude (col A) and Longitude (col B).
2. Run GetAddresses from Extensions → Apps Script (or add a custom menu if you prefer).
3. Results will appear in column C. A 1-second delay is included to respect API limits.

---

📦 Excel + Macro ZIP Package

This ZIP includes a macro-enabled workbook ready to go, plus the standalone VBA module.

👉 Download Excel Macro ZIP (macro pre-installed)

▶️ How to use

1. Extract the ZIP.
2. Open the included .xlsm file, click Enable Content if prompted.
3. Paste your coordinates (Lat in A, Lon in B) and run GetAddresses.

🧩 What’s inside

• .xlsm workbook: macro already embedded (fastest option).
• .bas file: the macro module, in case you want to import it into another workbook (ALT+F11 → File → Import File…).

---

ℹ️ Notes, Limits & Troubleshooting

• API: Uses OpenStreetMap’s Nominatim (no API key). Respect 1 request/second; practical free usage is light/medium volume.
• Slow or “Address not found”? Check your internet/firewall, confirm your coordinates are valid, and try again.
• Corporate environments: If web requests are blocked, ask IT to allow traffic to nominatim.openstreetmap.org.