The costless way to geocoding addresses in Excel – part1, via Google Sheets

Address geocoding is a very desired tool nowadays. This is because we might need to know the location of many places at once.  If for example, you want to have about 500 exact coordinates of your addresses, you won’t do them one by one, otherwise, it will last forever.
So far I have shown you some of the tools, which can do the address geocoding for you without any charge. The major drawback of them is the restricted upper limit of the operations. It usually cannot make more than 250 records at once. In another way, you can use Google Earth to convert 10x more. However, at this moment we are complicating our task because it starts to take longer than we would expect.
In this short part of the article, I would like to demonstrate to you how to address geocoding in Excel, the widest calculation software used in our offices.
Converting our addresses into the geometry is supposed to be very quick and convenient in MS Excel, as our work is confined to typing and dragging down the relevant formula.
You might be itching to ask – why am I showing you only the basic tools, which don’t offer bulk geocoding like 10,000 addresses or more?  At this stage, I want to demonstrate how to deal with a smaller amount of locations and put them in the right place in our Excel Spreadsheet. Next time you can expect an extension of this work covering also the process straight from your spreadsheet, and further in the future – with respect to the bulk address data.

There used to be 2 ways to do geocoding in Excel. You can install the extension, based on the VBA Excel or write your own VBA code. Firstly, stressing the “used to” expression, because these ways are not for free anymore. Secondly, you don’t need to write the VBA code anymore, since there are tools created especially for this occasion. You can find them i.e. on the GitHub page.

The sad thing is, that most of the applications aimed at addressing geocoding require API keys, which restrict the user to a small number of records. For example, if you want to geocode with Bing.com, be prepared for only 10,000 records in total. After that, you must create a new account, which can be awkward. Google API, unfortunately, is ruthless and wants us to pay for any amount of records since August 2018.
In this event, even if you place your Google API key into your VBA code, you won’t be able to do anything (Pic. 1).

Pic. 1 Address geocoding in Excel (VBA Excel macro) is not valid due to a lack of a proper Google API Key.

Unfortunately, the “AddressGeocode” formula keeps coming up with nothing for every single address. In this event, the VBA Excel code is not useful, unless we make it compatible with the Bing API key. Nonetheless, as mentioned above, Bing won’t allow you to geocode more than 10,000 addresses under 1 single API key. Afterward, the new profile and new API key at once.
There is one, quick solution for this, but not directly related to Excel Spreadsheet.
You can easily use Google Sheets, where the Geocode by Awesome Table plugin is available.
With this plugin, you are able to geocode up to 1,000 records per day, which is good. Remember to set up your plugin first, before you attend to this process. It will prevent you from unwanted situations, leading to complaints, that the tool is simply “not working”. Remember also, that in order to use Googe Sheets you must be signed into the Google account.
When you are ready, just create the new document in your Google Sheets account, where your addresses should be pasted (Pic. 2).

Pic. 2 The address list was pasted into the Google Sheets document.

Copying from Excel and pasting in Google Sheets in the same position prevents the initial order of our locations.
If everything is good, you can run the plugin, which at the very beginning will ask you about the main address column. It’s good to keep the column name in the first row. At least the tool knows the place to start with. Next, the plugin automatically creates the “Latitude” and “Longitude” columns for the result input. The process is quite slow but gives a decent effect (Pic. 3).

Pic. 2 The address geocoding process in Google Sheets. The Latitude and Longitude columns are created by the tool in order to place the result.

When everything is done, we can copy our data from Google Sheets (Pic. 3), and next paste them into our Excel document.

Pic. 3 Selection and copy geocoded addresses from Google Sheets to Excel.

If you are sure about the order of your address list, as I indicated earlier, it’s enough to copy the columns with coordinates only (Pic. 4), and paste them next to the existing address column in Excel (Pic. 5).

Pic. 4 Selection of the columns with coordinates only, taking into account, that their values correspond to the address array.

Pic. 5 The Google Sheets addresses are pasted in Excel.

Make sure, that you are selecting the Match Destination Formatting option. It will help you save time with further formatting. Your result will be automatically adjusted to the format of the existing cells. The other way is to save this sheet as a .xlsx file.
Finally, you have got your problem sorted (Pic. 6).

Pic. 6 Our geocoded address list in Excel.

The probes are a bit awkward, as we must go out from the Excel document and use the external tool. However, treading this way we can sort this problem out quickly. It doesn’t mean, that other solutions such as this are not available. This is one of the solutions.
A similar one can be found here. This one can also do the reverse geocoding for you, also in Google Sheets.

The process above applies to a smaller amount of records. It’s not the end of my tinkering with address geocoding.  The second part of this article will bring new ways to deal with this task. Further, as the continuation of this article, you will be taught how to deal with bulk address geocoding.

Mariusz Krukar

Links:

1. Mapdevelopers.com – batch geocoding (100 addresses)
2. MapYourList.com – batch geocoding (up to 250 addresses)
3. https://gisgeography.com/reverse-geocoding-services-addresses-free-paid/
4. http://more.stevemorse.org/latlonbatch2.html? (monthly limit 15000)
5. http://excelgeocodingtool.com/
6. https://www.ilovefreesoftware.com/12/tutorial/geocode-address-lat-long-excel.html
7. https://weblogs.asp.net/wkriebel/using-excel-to-geocode-with-bing-maps-web-services
8. https://www.shayatik.com/2014/04/batch-geocode-addresses-using-bing-maps-api-and-an-excel-worksheet/
9. https://myengineeringworld.net/2014/06/geocoding-vba-google-api.html
10. Google Sheets awesome table geocode
11. Auto populate longitude & latitude in Excel (Google API Key required)
12. Geocode by Awesome Table – tutorial
13. Geocode by Awesome Table FAQ
14. https://vilimpoc.org/blog/2013/07/11/google-spreadsheet-geocoding-macro/

Forums:

1. https://gis.stackexchange.com/questions/70586/geocoding-using-excel-spreadsheet
2. https://gis.stackexchange.com/questions/102839/how-to-geocode-a-google-spreadsheet/340215#340215

Youtube:

Read also:

1. Excel Spreadsheet batch data geocoding for .geojson and .json files