Batch Geocoding Excel Spreadsheets

If you’ve ever made a week’s worth of meals in one day, paid your bills a stack at a time, or shopped at a membership club like Costco, you understand the concept of “batching.” It’s more efficient than handling each of those activities one at a time. You can wait until you have multiple tasks that are similar and then batch them at a particular time. Just as it makes sense in your personal life, batching is an important concept when working with data, including the stuff you store in Excel.

You may be storing many types of data in Excel, including financial, accounting, and sales data. Any of this may also include locations, such as cities, addresses, and postal codes. These can be converted into geographic coordinates—either one at a time or all at once. Anytime you have more than one location to geocode, that’s where you’ll use batch geocoding. So let’s dive into more about what batch geocoding is and some of the popular ways to batch geocode an Excel spreadsheet.

Batch Geocoding 101

Geocoding is the process of turning text location descriptions into numbered geographic coordinates, usually latitude and longitude. These pairs identify a point on the Earth’s surface. So what type of text-based descriptions can be geocoded?

The most common geocoding task is one or more full addresses, typically including the city and sometimes state or country. However, many geocoders accept several other types of locations. You can send city names (with or without state/country), postal codes, and state or country names. Some batch geocoders can even take common landmarks, business names, or place names, such as the largest houses in the U.S. like Biltmore Estate or Oheka Castle.

Geocoding larger geographic areas (such as entire countries) typically returns the center point of a place. With that overview, let’s take a look at how to geocode addresses in Excel.

Two Ways to Batch Geocode Your Spreadsheets

There are many great methods of batch geocoding your Excel or Google Sheets data, though the options range in price and their limitations. While multiple free ways exist (we’ll show you two), they can have downsides of address limits and difficulty.

Install a Google Sheets Add-On

This method of batch geocoding your spreadsheet works whether your data is stored in Excel or Google Sheets. It’s also free for 1,000 records per day. If you’re interested in this option, here’s how to go about it.

  1. In your web browser, navigate to Google Drive
  2. Click New > Google Sheets > Blank spreadsheet
  3. Either copy (Ctrl+C or Cmd+C) and paste (Ctrl+V or Cmd+V) your data from Excel or add your data directly into Google Sheets
  4. Download the Geocode by Awesome Table plugin for Google Sheets
  5. In the navigation bar, opt for Add-ons > Geocode by Awesome Table > Start Geocoding
  6. Determine if you want to continue working in Google Sheets or copy and paste the newly geocoded data from Google Sheets back into your Excel spreadsheet. Then you’re done!

However, this option has two downsides. First, for those with over 1,000 locations, this isn’t ideal due to its limits. Secondly, this method requires navigation to multiple external platforms depending on the spreadsheet tool you begin with. It isn’t possible to complete the steps all in Excel, nor is it even possible to remain entirely in Google Sheets, as you must install the add-on.

Another method that involves Google Sheets is importing JSON data in Google Sheets.

But let’s move on to a method that allows you to batch geocode more than 1,000 rows of your spreadsheet without switching between too many platforms.

Use Bing’s Free API

Here’s another option, in the event that you have more than 1,000 records and you’d rather not install an add-on. Or maybe you just dislike Google Sheets (let us change your mind; here’s why Google Sheets is better than Excel.)

This is where one of the most popular and the fastest geocoders come in. A Bing API key allows users to geocode 10,000 addresses.

Follow the steps below to take advantage of this option:

  1. Head over to http://excelgeocodingtool.com/
  2. Hit Download Excel File
  3. Open the downloaded file and enable Macros
  4. Create a Bing API key and copy and paste it into the Bing Maps Key field
  5. On the second sheet entitled Geocode, paste your addresses into the location column, opt to Geocode all rows, and you’re done!

Beyond 10,000, you must make a new account to obtain a new key, which can be a hassle. Plus, as you’ve seen, both of these options can be a bit difficult. Let’s check out an easier alternative.

An Easier Alternative

There’s more out there than clever hacks of tools that were never meant for batch geocoding. Instead of low quantity limits, add-on installations, and API keys, it’s easier to use an established tool with the sole purpose of batch geocoding your spreadsheets. BatchGeo enables you to simply copy and paste in any spreadsheet data.

To get started batch geocoding your spreadsheet, follow our simple steps below:

  1. Open your spreadsheet
  2. Select (Ctrl+A or Cmd+A) and copy (Ctrl+C or Cmd+C) your data
  3. Open your web browser and navigate to batchgeo.com
  4. Click on the location data box with the example data in it, then paste (Ctrl+V or Cmd+V) your own data
  5. Check to make sure you have the proper location data columns available by clicking “Validate and Set Options”
  6. Select the proper location column from each drop-down
  7. Click “Make Map” and watch as the geocoder performs its process

Our web-based tool’s free version is more than enough for most users while our Pro option enables the quick mapping of 20,000 addresses at a time. For even more on how to batch geocode addresses quickly and easily, check out our page on the subject.

A Step Further: Mapping Addresses From Excel

With an understanding of batch geocoding and some options to utilize it for your spreadsheets—including our always easy geocoder— it’s time to take it one step further. Note that our tool also enables you to create custom maps, like the one below.

View Largest houses in the U.S. in a full screen map

We mapped this Wikipedia list of the 100 largest houses in the U.S. in seconds thanks to our mapping tool. Batch geocode and map your own spreadsheets today at batchgeo.com.