How to geocode a CSV or Excel file with Google Sheets

The short answer

To geocode a CSV file, import it into Google Sheets with File → Import, then use MapRows to add latitude and longitude in new columns. Check the row statuses and matched addresses, and choose File → Download → Comma-separated values to save the current sheet as a CSV. You can use the same workflow to geocode an Excel file after importing it.

MapRows Free includes 100 lookups per day. One address geocoded is one lookup. Pro includes 2,500 lookups per day, subject to fair use. The add-on is useful when you want to work in a sheet, review statuses and resume a larger task. It is not necessary for every file: the free Census batch geocoder accepts US addresses, including Puerto Rico, in files of up to 10,000 rows and 5 MB.

For a US-only file over the MapRows Free allowance, Census is a free route if its input format and coverage fit your addresses. An Apps Script is another free option for a sheet of addresses. Both alternatives have their own preparation and checking steps, which we cover below. Whichever method you choose, coordinates are matches to check, not a guarantee that every address was found precisely.

Import and prepare your file

Keep your original CSV or Excel file as a reference. In Google Sheets, choose File → Import, select the file and review the import choices in your account. After importing, look at the sheet before running a geocoder. You need one row per place and a header row that identifies the address columns.

1. Check that each address is on its intended row and that the header describes the contents below it. Remove blank rows from the list so they do not distract from the results.
2. Compare a few imported addresses with the source, including one with an accented character and one containing commas. Check the file encoding and import options in your account if characters do not appear correctly.
3. Check ZIP codes that begin with zero. In the import dialog, check the option for converting text to numbers. Compare the imported ZIP values with the original before proceeding.
4. Check that the data is in the intended columns. CSV uses quoting for commas inside a field, and the import handles correctly quoted fields. An address such as "123 Main St, Springfield, IL 62701" is one field when its enclosing quotes are part of the CSV format.
5. Give the tab a clear purpose and retain any identifier that lets you connect the output to the original record. Check several IDs and addresses together after import.

A leading zero matters because 02108 and the displayed number 2108 are not the same text. If a five-digit ZIP has already become the number 2108 in A2, =TEXT(A2, "00000") returns 02108. Compare the repaired value with the source. This formula pads to five digits; it does not recover information that was missing from the source or repair every postal-code format.

If a comma has put part of an address in the wrong column, inspect the original CSV and its quoting before combining cells. Do not assume every comma marks the boundary between street, city and state: a street field can also contain a unit. Our address-splitting guide explains why a simple split can misplace parts when the number of commas varies.

Check the imported sheet for a single usable header row. MapRows reads the selected header row to suggest columns, so a title above the real headers or a repeated header within the data can make the list harder to review. Preserve the source file while you tidy the working sheet, especially if you need to compare a suspicious result later.

Choose the address columns

MapRows accepts one full-address column or several address-part columns in order: Street, City, State and ZIP. You do not have to split a complete address just to geocode it. If the file already has separate parts, keep those parts in their own columns and select them in the documented order.

For example, one row might contain 1600 Main St, Springfield, IL 62701 in a single Address cell. Another file might have 1600 Main St, Springfield, IL and 62701 in four columns. Both layouts provide the parts needed for the MapRows selection. Use the actual headers and values in your own file, and check that every selected column contains the part its header describes.

Complete, consistent addresses are easier to match. A town name alone can be ambiguous, so include city, state and ZIP when you have them. Add the country when a list spans countries. If a match is wrong, removing suite or unit noise from the geocoding input may help: geocoders match buildings rather than suites. Keep the original address available for comparison.

If another method needs one address column, you can join four adjacent parts. With Street through ZIP in B2:E2, =TEXTJOIN(", ", TRUE, B2:E2) joins the nonempty cells with commas. For the four values above, the result is 1600 Main St, Springfield, IL, 62701. Copy the formula down and inspect rows with missing parts. Joining text does not supply a missing city or correct a typo.

Geocode the rows with MapRows

Install MapRows from its Google Workspace Marketplace page if you have not already done so. In the imported spreadsheet, choose Extensions → MapRows → Open MapRows. The first time, Google asks you to allow the permissions. The sidebar shows usage and the reset time.

1. Open the Geocode tab. Choose the sheet containing the imported addresses and select its header row.
2. Tick the full-address column, or tick the Street, City, State and ZIP columns in order. Address-like headers may already be selected, so check them against your actual data.
3. Optionally choose Limit to a country when a country filter will help distinguish matches. Check the setting before running a list that spans countries.
4. Choose the output. Coordinates gives latitude and longitude. Standard adds formatted address, city, state, postcode and country. Everything adds further fields including house number, street, county, codes, confidence and match type. You can also tick fields individually.
5. Choose All rows or Selected rows, then press Geocode. For a first check, selected rows let you inspect a small sample before processing the rest.

Results appear in new columns to the right of your data. MapRows does not edit your own columns or formulas. Its new headers have notes starting with MapRows:; keep those notes in the working sheet so later runs can reuse the result columns instead of adding new ones.

If you only need coordinates in the final CSV, consider including the formatted address while you review. It gives you something concrete to compare with your input. You can make a separate export tab with fewer columns after checking. A pair of numbers alone does not tell you whether the match refers to the intended street or only to a broader area.

Check statuses and matches

Filter the status column and review the results before exporting. Start with LOW_CONFIDENCE, NO_MATCH and ERROR, then check any unexpected blanks. A successful-looking coordinate pair can still be wrong, so compare formatted addresses with the original input for rows that matter.

• OK means a match with good confidence. Check important rows anyway, particularly where the address could refer to more than one place.
• LOW_CONFIDENCE means a match below 0.6 confidence, often only a street or town. Compare the formatted address, add a city or ZIP, or use a country filter where appropriate.
• NO_MATCH means nothing was found. Check spelling and add missing address parts before trying the affected row again.
• ERROR: ... indicates a temporary problem, such as a rate limit. Read the status and run again; only unfinished rows are retried.
• EMPTY_INPUT means the selected input cells were blank. Check whether the row is intentionally empty or the wrong columns were selected.

Look for many rows with identical coordinates. They may have matched a city or ZIP center instead of individual buildings. Compare the formatted addresses with the inputs and pay particular attention to missing street details. New buildings, rural addresses and other incomplete data can also produce wrong or incomplete results.

Skip rows already done is on by default. It skips rows with OK or LOW_CONFIDENCE statuses on a rerun. That saves lookups for finished rows, but it also means a low-confidence match will not automatically be replaced simply because you corrected its input. Check the skip setting when you deliberately want to geocode such a row again, and inspect the new result.

For a more detailed review of incomplete addresses, ambiguous places and failed matches, see how to fix addresses that will not geocode. Keep the original inputs beside the results until you have finished reviewing them.

Export the results

MapRows writes its results into ordinary cells in the sheet. Once you have checked them, select the tab you want to export and choose File → Download → Comma-separated values. The CSV download is for the current sheet only. Open the downloaded file and check the headers, row count, ZIP codes, statuses and a few coordinates against the working sheet.

The MapRows: header notes do not export to CSV. Keep the working spreadsheet and its notes if you plan to run MapRows again and want it to reuse the existing result columns. The exported file is useful for handing off cell values; it is not a replacement for those notes in the working sheet.

If the recipient needs only an ID, address, latitude and longitude, make a copy tab and delete the unwanted columns from that copy before downloading it. Keep the original working tab intact for review and later runs. Hiding columns is not a reliable way to leave them out of a CSV: hidden columns still export. Check your file after download to confirm that it contains the intended columns.

For an Excel file, check your Download menu for File → Download → Microsoft Excel (.xlsx). Open the downloaded file and compare a few rows with the sheet. Whether you export CSV or Excel, make sure the person using the file understands which column is latitude and which is longitude, and whether any unmatched rows remain.

MapRows results come from Geoapify using OpenStreetMap data. You may keep the results stored in your sheet. If you publish them, keep the attribution © OpenStreetMap contributors. Include that attribution with the published results; the header notes are not carried into a CSV.

Plan for large files and daily limits

MapRows Free allows 100 lookups per day; Pro allows 2,500 per day, subject to fair use. One address geocoded is one lookup. Allowances reset at 00:00 UTC, and the sidebar shows the reset time in your time zone. Other lookups you use that day leave less of the allowance for your address file.

For a planning estimate, divide the number of addresses by the daily allowance and round up. With a full allowance available each day, 250 ÷ 100 = 2.5, so 250 addresses require at least three Free daily allowances. With Pro, 250 ÷ 2,500 = 0.1, so they fit within one daily allowance. A list of 5,000 addresses requires at least two full Pro daily allowances. These estimates assume one lookup per address and do not reserve capacity.

Large sheets run in short chunks with a progress card. You can pause a task. If the allowance or MapRows’ shared daily capacity runs out, the task pauses; press Resume after the reset to continue from the next unfinished row. If you close the sidebar during a task, reopen it and press Resume. Progress is kept.

There can be one unfinished task per spreadsheet. Starting a new one asks before replacing it, so check the progress card before starting another job. Shared capacity can pause a task even when your own allowance is not exhausted. For more on preparing and reviewing a larger list, see our large-list guide.

MapRows Pro costs $9 per month or $59 per year, tax included. Pro also lets you add your own Geoapify key; MapRows limits then stop applying and your Geoapify plan’s limits apply instead. Compare the options on the pricing page. For an occasional US-only file, check the free Census option before deciding that you need an upgrade.

Free alternatives for a CSV or Excel file

The Census batch geocoder is free for US addresses, including Puerto Rico. It accepts up to 10,000 rows and 5 MB per file, and it knows street addresses rather than business names. For a qualifying file larger than the MapRows Free daily allowance, it can be a practical free choice, though it requires a CSV round trip.

1. Prepare a tab with exactly five columns in this order: Unique ID, Street address, City, State and ZIP. Use a unique row number as the ID and remove the header row from the file you will upload.
2. Download that tab as a CSV. On the Census site, choose Find Locations Using… → Address Batch, upload the file and download the results.
3. Import the results into a new sheet tab. Match them back to the original list by ID using XLOOKUP or VLOOKUP; do not assume the returned rows are in the original order.
4. Review Match, No_Match and Tie results. Compare non-exact matches with the input. Correct incomplete addresses and resubmit only unmatched rows.

Census returns coordinates in one field as longitude,latitude, longitude first. Split that field carefully before labeling columns. For example, -77.035189204737,38.898702605246 has longitude -77.035189204737 and latitude 38.898702605246. The geocoding guide shows formulas for matching Census results by ID and extracting each coordinate.

The same guide provides a free Apps Script method for a selected address column. It writes latitude, longitude and matched address as plain values in three columns immediately to the right, so insert three empty columns first. The script skips rows that already have a latitude and can be run again after a quota stop or timeout. Check current Apps Script quotas and Google’s terms in your account, especially if you intend to keep results or display them on a non-Google map.

For a handful of addresses, the guide also describes copying coordinates from Google Maps. That can be enough for a small file, but manual copying is easy to get wrong as the list grows. Whichever free method you use, compare matches with the source and retain the ID that connects each result to its original row.

Common problems

A match is in the wrong country. Add the country to ambiguous input and check Limit to a country on the Geocode tab. Compare the formatted address with the source. A country filter should fit the rows you are running, so check it before processing a mixed-country file.

A ZIP code became a number. Compare it with the original file and check the import dialog’s text-to-number conversion option. For a five-digit ZIP, =TEXT(A2, "00000") can turn numeric 2108 into text 02108. Check the source rather than assuming every short number needs the same repair.

Rows were skipped after an edit. Check the row status and Skip rows already done. It is on by default and skips OK and LOW_CONFIDENCE rows. When you intend to replace a completed match after correcting the address, check that setting and review the result of the rerun.

The task paused. Check the progress card and the usage and reset time in the sidebar. The daily allowance or shared capacity may have run out. Press Resume after the reset; the task continues from the next unfinished row. If you closed the sidebar, reopen it first.

The export has unexpected columns. Select the intended tab and inspect the downloaded CSV. Hidden columns still export. Make a copy tab and remove unwanted columns there, then download and check the new file. Keep the working tab and its MapRows notes for later runs.

Several addresses have the same coordinates. Compare their formatted addresses with the inputs. They may have fallen back to a shared city or ZIP center. Add missing street, city or ZIP details where available and review the affected matches.

Frequently asked questions

Related

• Geocode addresses in Google Sheets
• Geocode a large list of addresses
• Fix addresses that will not geocode
• Split an address into columns
• Map addresses from Google Sheets
• MapRows documentation
• All guides

Disclosure: we make MapRows, the Google Sheets add-on mentioned in this guide. The Census batch geocoder works without it for US street addresses, including Puerto Rico, within the stated file limits. The Apps Script in our geocoding guide also works without MapRows.

Google Sheets, Google Maps and Google Workspace are trademarks of Google LLC. MapRows and Cellbrook are not affiliated with or endorsed by Google.