How to geocode a list of addresses in Google Sheets

To geocode a list of addresses in Google Sheets, paste your addresses into column A, such as A2:A50, and enter =GEOCODE(A2:A50) in B2. InstaMaps processes the range and spills the latitude and longitude into adjacent columns automatically without requiring any scripts or macros.

This method is built for logistics coordinators managing 5 delivery crews, real estate analysts evaluating a 200-property portfolio, or event planners mapping 47 guest stops. The end state is a clean, coordinate-rich dataset ready for distance calculations or live map plotting.

The recipe: Geocode a list of addresses

1. Paste your raw address list into a single column. For a dataset of 85 delivery drop-offs, your addresses will occupy cells A2 through A86 in Column A. Leave row 1 for your header text (e.g., 'Destination Address').

2. Open the formula builder by clicking Extensions > InstaMaps > Enable formulas in the top menu. This opens the add-on sidebar. The sidebar lets you insert exact formulas into your sheet without manual typing. Alternatively, click cell B2 and type =GEOCODE(A2:A86).

3. Press Enter. Because Google Sheets supports dynamic arrays, the single formula placed in B2 calculates the coordinates for the entire A2:A86 range.

4. The results spill into the adjacent columns automatically. Column B will display the latitude (for example, 52.3740), and Column C will display the corresponding longitude (for example, 4.8897). The first formula cell (B2) controls the entire array; you cannot delete C4 without deleting the parent function in B2.

5. To visualise these 85 coordinates, move to cell E2 and type =INSTAMAP(B2:C86). This returns a live, hosted shareable map URL. If you change the text in A45 from a street name to a postcode, the coordinate in B45:C45 updates, and the hosted map reflects the new pin location automatically.

1. Use =GEOCODE(A2:A86) to process exactly 85 rows. If you use =GEOCODE(A2:A), the formula continually monitors the entire column A for new entries, spilling coordinates whenever you append a new address at the bottom of the list.

2. The free tier allows 100 lookups per day, which increases to 1,000 lookups per day by unlocking your email in the add-on menu.

3. Use the sidebar's 'Build-the-workflow' button to chain formulas together. This tool writes formula chains that link the geocoded coordinates in B2:C86 directly into a distance matrix or a navigation link generator like =ROUTE_LINK().

Prepping data with CLEAN_ADDRESS

Raw address data copied from emails or CRM exports often contains formatting inconsistencies. You might encounter lowercase text, trailing spaces, or missing punctuation. These variations reduce geocoding accuracy. Before applying the geocoder, standardise the text inputs.

If your raw, unformatted addresses sit in column A (cells A2:A150), click cell B2 and enter =CLEAN_ADDRESS(A2:A150). This function parses the text and returns a standardised format. For example, if cell A12 contains the string '10 downing street, london ' (note the trailing spaces and lack of capitalisation), the formula in B12 will output '10 Downing Street, London'.

The function trims excess whitespace, corrects capitalisation, and prepares the string for precise coordinate plotting. Because =CLEAN_ADDRESS(A2:A150) spills down the entire column, it processes all 149 rows instantly.

You then point your geocoder at the cleaned data. In cell C2, enter =GEOCODE(B2:B150). The coordinates generated from the cleaned column B will have a significantly higher accuracy rate than a geocoder pointed directly at the messy column A.

1. Use =CLEAN_ADDRESS() on a dedicated column before running =GEOCODE() to prevent typos from causing location failures.

2. If you are using the sidebar (Extensions > InstaMaps > Enable formulas), you can use the 'Build-the-workflow' button to chain =CLEAN_ADDRESS() and =GEOCODE() together automatically, mapping column A to column B, and column B to columns C and D without manual typing.

Handling geocoding failures and errors

When =GEOCODE() encounters a typo, an invalid address, or a missing postal code, it returns an #ERROR message in the spill array rather than a coordinate pair. A second common failure point is exceeding the daily quota. The free tier allows 100 lookups per day, but expanding this to 1,000 lookups per day via the free email unlock resolves most volume issues. If you attempt to geocode 450 rows with the default 100-lookup limit, rows 101 through 450 will return a quota error.

To identify and resolve these failures, apply a standard Google Sheets filter to your header row. Click cell C1 (above your longitude spill range), activate the filter (Data > Create a filter), and sort by descending order. The #ERROR cells will group at the top of the column.

Inspect the corresponding address in column B. In many cases, the address lacks a postcode. If cell A45 reads '221B Baker Street', the geocoder lacks the precision to plot it. You can resolve this by using the =POSTCODE() function on a secondary dataset to append missing zip codes, or by manually verifying the address.

If the errors are simply due to the daily lookup limit, the formula will automatically resolve them. Because the array updates dynamically, when the daily quota resets, the #ERROR messages in cells C101:D101 and below will automatically recalculate and populate with the correct latitudes and longitudes without requiring you to rewrite the =GEOCODE(B2:B450) formula.

Worked example: Mapping 200 properties for a 5-crew farm inspection

A regional agricultural agency needs to assign 200 farm properties across 5 inspection crews. The raw data sits in Sheet1!A2:A201, containing inconsistent casing, missing commas, and trailing spaces.

1. Clean the inputs. In cell B2, enter =CLEAN_ADDRESS(A2:A201). The formula spills down to B201, standardising the formatting (e.g., changing "123 fake rd" to "123 Fake Rd") so the geocoder can parse the text accurately.

2. Geocode the list. In cell C2, type =GEOCODE(B2:B201). This outputs a two-column array: latitudes in C2:C201 and longitudes in D2:D201. Failure handling is built into the spill array. If row 84 contains a non-existent location like "Nowhere City", cell C84 returns #ERROR! without halting the remaining 199 calculations.

3. Assign crews. Add a new column in E, typing "Crew 1" through "Crew 5" in E2:E201.

4. Map the coordinates. In cell G2, input =INSTAMAP(C2:D201, E2:E201). The =INSTAMAP() function returns a live, hosted URL. Opening this link in a browser displays all 200 properties as pins on a map. The E2:E201 range acts as a grouping variable, colour-coding the pins by crew assignment.

5. Update the sheet. If you fix the text in A84 to "Nowhere Ct", B84 cleans it, C84 and D84 update with valid coordinates, and the hosted map instantly adds the new pin for Crew 2 without requiring a manual refresh.

If a crew lead needs driving directions for a subset of their morning stops, they can pass specific rows to =ROUTE_LINK(). For example, =ROUTE_LINK(C12:C22, D12:D22) generates a Google Maps URL containing the 11 stops in the exact sequence they appear in the sheet.

When =GEOCODE() encounters failures, you can isolate them. Wrapping the output in standard Sheets functions like =FILTER(B2:B201, C2:C201="#ERROR!") compiles a list of bad addresses. You investigate these specific entries, correct the text in column A, and the geocoding array recalculates only the failed rows, pulling new coordinates without manual copy-pasting.

Limits and honest alternatives

InstaMaps provides a free tier of 100 lookups per day. If you verify your account via a free email unlock, this limit increases to 1,000 lookups per day. These are hard daily caps enforced at the server level to prevent quota abuse.

If you paste 1,500 addresses into A2:A1501 and run =GEOCODE(), the add-on processes the first 1,000 rows. The remaining 500 rows will wait until the next 24-hour cycle. To clear the backlog immediately, you can split the data across multiple Google Sheets documents or wait for the daily quota to reset.

The primary alternative is the Google Maps Platform API. To replicate this workflow via the API, you must write a Python or Apps Script program, configure a billing account, secure an API key, and parse JSON responses. This takes several hours of developer time and costs $5.00 per 1,000 requests after the initial $200 monthly credit.

You should switch to a developer API when you need to process massive datasets exceeding 10,000 addresses per day. If you have a list of 50,000 addresses, using InstaMaps will take 50 days at the unlocked rate. In that scenario, paying the $250 API bill to process the batch in 10 minutes is the logical choice.

You must also consider the per-minute request limits of the Google Maps Platform API, which often requires implementing exponential backoff in your code to avoid 429 Too Many Requests errors.

InstaMaps handles the rate-limiting automatically. You simply paste the formula and let the sheet populate. If you reach the daily limit, the =GEOCODE() function pauses, displaying the last successfully processed row, and resumes processing automatically when the quota refills.

Who this approach is for

This approach is designed for logistics coordinators, local delivery dispatchers, and field service managers who need to map addresses without hiring a developer.

It is ideal for users managing spreadsheets of 50 to 1,000 rows. If you are a dispatcher routing 47 daily stops for 5 delivery vans, the =INSTAMAP() and =ROUTE_LINK() formulas will plot your pins and generate driving links quickly. The tool assumes you want your data to remain in Google Sheets, benefiting from standard formulas and the built-in sidebar at Extensions > InstaMaps > Enable formulas.

It is not suited for professional cartographers, urban planners, or GIS analysts. If your work requires drawing buffer zones, performing spatial joins on polygon shapefiles, or analysing raster elevation data, you need dedicated geographic information system software like QGIS or ArcGIS. InstaMaps converts text to coordinates and provides routing links; it does not calculate land mass or perform topological spatial analysis.

If you are a sales manager assigning 400 leads to 10 regional representatives, you can geocode the leads, use =CLOSEST_TO() to find the nearest rep, and map the territories with =INSTAMAP().

Conversely, if you are a data scientist running predictive models on 500,000 mobile device pings, this add-on will frustrate you. The 1,000 daily lookup limit makes it inadequate for big data pipelines, and Google Sheets itself slows down when handling hundreds of thousands of complex array formulas.

Finally, if you are an event organiser tracking the locations of 200 vendors, the free tier handles the entire list in a single day. The resulting URL is easily shared with security teams via a standard web browser. The tool bridges the gap between a basic spreadsheet and a commercial mapping tool, provided your dataset stays within the row limits of standard Google Sheets.

Common Questions