📍 Free Geocoding Templates: Get Coordinates from Address

Need to quickly convert street addresses into Latitude & Longitude? These free Excel and Google Sheets templates let you enter any address and pull GPS coordinates using the OpenStreetMap Nominatim API. No API key required!

---

📥 Download the Templates

🟢 Excel Geocoding Template (.xlsx)

⚠️ First Time?

This starter workbook works in Excel 2016 or later. 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 AddressToCoordinates_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. Enable macros: File → Options → Trust Center → Trust Center Settings → Macro Settings.
5. Enter Address in column A.
6. Run the macro: Developer → Macros → GetCoordinates → Run.

📜 VBA Macro Code (Forward Geocoding)

Option Explicit

Sub GetCoordinates()
    Dim http As Object, json As String
    Dim url As String, i As Long, lastRow As Long
    Dim addr As String

    Application.ScreenUpdating = False

    lastRow = Cells(Rows.Count, 1).End(xlUp).Row
    For i = 2 To lastRow
        addr = Cells(i, 1).Value
        If Len(addr) > 0 Then
            url = "https://nominatim.openstreetmap.org/search?format=json&q=" & Replace(addr, " ", "+")
            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, 2).Value = ExtractJsonValue(json, "lat")
                Cells(i, 3).Value = ExtractJsonValue(json, "lon")
            Else
                Cells(i, 2).Value = "Error"
                Cells(i, 3).Value = "Error"
            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

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

    valueStart = startPos + Len(token)
    valueEnd = InStr(valueStart, json, """")
    ExtractJsonValue = Mid$(json, valueStart, valueEnd - valueStart)
End Function

🔁 Next time you use it

• Open your saved .xlsm, enable macros, paste new addresses, and run GetCoordinates.

---

🟡 Google Sheets Template

No Excel? Use the Google Sheets version with built-in Apps Script.

👉 Open Google Sheets Template – Copy of Address to Coordinates

✅ First-time setup

1. Click the link and Make a copy.
2. Go to Extensions → Apps Script, paste the script below if missing, and Run.
3. Authorize the script the first time (Google will warn about permissions).

📜 Google Apps Script Code

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

function GetCoordinates() {
  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, 1).getValues();
  for (let i = 0; i < data.length; i++) {
    const address = data[i][0];
    if (address !== '') {
      try {
        const url = `https://nominatim.openstreetmap.org/search?format=json&q=${encodeURIComponent(address)}`;
        const response = UrlFetchApp.fetch(url, {
          headers: { 'User-Agent': 'GoogleSheetsGeocoder - basicexceltutorial.com' },
          muteHttpExceptions: true
        });
        const results = JSON.parse(response.getContentText());
        if (results.length > 0) {
          sheet.getRange(i + startRow, 2).setValue(results[0].lat);
          sheet.getRange(i + startRow, 3).setValue(results[0].lon);
        } else {
          sheet.getRange(i + startRow, 2).setValue("Not found");
          sheet.getRange(i + startRow, 3).setValue("Not found");
        }
        Utilities.sleep(1000);
      } catch (e) {
        sheet.getRange(i + startRow, 2).setValue("Error");
        sheet.getRange(i + startRow, 3).setValue("Error");
      }
    }
  }
}

▶️ How to use

1. Enter addresses in column A.
2. Run GetCoordinates (from the custom Geocoding menu).
3. Latitude and Longitude will fill into columns B & C.

---

📦 Excel + Macro ZIP Package

Want to skip setup? Download the pre-made macro-enabled workbook.

👉 Download ZIP Package

What’s inside

• .xlsm workbook: ready-to-use with macros installed.
• .bas file: standalone VBA module (import if needed).

---

ℹ️ Notes & API Limits

• Uses OpenStreetMap Nominatim API (no API key needed).
• Rate limit: 1 request per second. Templates already wait 1 sec per lookup.
• Best for light/medium use. For bulk geocoding, consider a paid API.