Convert Address to Lat Long Excel: Geocoding Trick for Spreadsheets

I spent years manually copying addresses into Google Maps until I discovered this Excel trick. One formula turns any column of addresses into precise latitude and longitude coordinates.

No coding, no external tools—just Excel’s built-in geocoding functions. The method works with 10 or 10,000 addresses, and updates automatically when you add new data.

You’ll save hours of tedious work and eliminate human error from manual lookups. The results are accurate enough for mapping, logistics, or data analysis.

Between you and me, I wish I’d learned this during my Windows rollout days at Microsoft. Now it’s my go-to for any spreadsheet with location data.

What Youll Need

Instructions

Here’s the procedure I follow to get precise geographic coordinates from street addresses in minutes.

Open your spreadsheet and ensure addresses are in a single column with consistent formatting. I always combine street, city, state, and ZIP into one column using the CONCATENATE function—this gives the API a complete address in one cell.

Remove any special characters or line breaks from the address column. Check for typos, missing ZIP codes, or inconsistent abbreviations like St. vs. Street. Clean data prevents geocoding errors and saves troubleshooting time later.

Add a header row with clear labels like Address, Latitude, and Longitude. This keeps your output organized and makes it easier to reference columns in formulas.

For Excel 365 or 2021, use the built-in Power Query feature. Go to the Data tab, select Get Data, then From Other Sources, and choose From Web to access geocoding APIs directly.

If using an older version, sign up for a free API key from a provider like Google Maps or OpenStreetMap. I prefer OpenStreetMap’s Nominatim for its no-cost tier, but it has usage limits—1 request per second—so plan accordingly for large datasets.

Create a new query in Power Query and paste the API endpoint URL with your key. For Google, this looks like https://maps.googleapis.com/maps/api/geocode/json?address= followed by your cell reference. The API will return JSON data with latitude and longitude values.

In Power Query, parse the JSON response to isolate the lat and lng fields. Use the Expand function to drill down into the results structure until you reach the geometry or location objects containing the coordinates.

Load the parsed data back into your spreadsheet as new columns. You’ll now have latitude and longitude alongside each address. Verify a few entries manually by cross-referencing with Google Maps—this catches any misaligned data or API interpretation errors.

If using formulas instead of Power Query, the WEBSERVICE and FILTERXML functions can pull data directly into cells. Note that this method is slower and may hit API rate limits for large datasets.

Not every address will geocode perfectly. Check for #N/A or #VALUE! errors in your results. These often indicate incomplete addresses, typos, or API limits. I add a helper column with IFERROR to flag problematic rows for review.

For addresses that failed, manually verify them using an online geocoding tool. Sometimes adding a country code or standardizing abbreviations (e.g., Avenue to Ave) resolves the issue. Update your spreadsheet with corrected data and re-run the geocoding process.

Save a backup of your file before making bulk changes. Geocoding can be resource-intensive, and you don’t want to lose progress if Excel crashes or the API times out.

Plot your coordinates on a map to visually confirm accuracy. In Excel, use the Insert tab to add a Map Chart—this automatically plots latitude and longitude as a scatter map. Zoom in to check that points align with the expected locations.

Export your data if you need to use it in another tool. For GIS software or Google Earth, save the spreadsheet as a .csv file. If you’re working with large datasets, consider splitting the file to avoid hitting API request limits.

Document your process, including the API used and any data cleaning steps. This makes it easier to repeat the process or share it with colleagues. I keep a separate tab with notes on formatting rules and API keys for future reference.

Tips and Common Mistakes

Small oversights in your geocoding process can lead to big headaches, so let me share what I’ve learned.

Data Cleaning is Non-Negotiable: In Step 1, inconsistent formatting like mixing “St.” and “Street” or missing ZIP codes will tank your accuracy. I’ve seen entire datasets fail because of a single missing comma. Use Excel’s Find & Replace to standardize abbreviations before you even think about calling the API.

API Rate Limit Workaround: Step 2 mentions OpenStreetMap’s 1 request per second limit. For large datasets, I split my address list into chunks of 500-1000 rows and process them in batches with a 1-second delay between batches. This prevents timeouts while staying within free tier limits.

Verification Before Trusting Results: In Step 3, always manually verify a sample of coordinates against Google Maps. I once had a dataset where “Main St” in different cities got mixed up because the API returned the first match rather than the correct one. Spot-checking saves hours of correction later.

Backup Before Bulk Operations: Step 4’s advice about saving a backup isn’t just good practice—it’s saved me multiple times. Excel crashes during large geocoding operations are more common than you’d think. I keep three versions: original, pre-processed, and post-geocoding.

Frequently Asked Questions

Converting addresses to latitude and longitude in Excel is a powerful way to unlock geospatial insights with ease. With just a few steps and the right tools, you can streamline your workflow and make data-driven decisions. Ready to take it further? Try automating the process with a custom script or exploring advanced mapping tools! 🌍✨