This tutorial talks about how to geocode address to Lat Long in Excel. One can easily geocode address in MS Excel using an open source script based on Google Geocoding API.
This script only sends the address parameters for obtaining the coordinates. While geocoding addresses in bulk, it will show the same coordinates for same city; in that case, use zip codes to improve the accuracy. This script has a limit of 2500 requests per day, so keep that in mind. If you are using Google Sheets instead of MS Excel, you can check out our tutorial How To Geocode Address To Lat Long In Google Sheets.
So, let’s dive in this step by step tutorial for geocoding addresses in bulk using MS Excel.
How To Geocode Address To Coordinates In Excel?
This tutorial has two parts:
- Creating An Excel Add-In For Geocoding Address To Coordinates.
- How To Use GeoCoordinates Add-in In Any Excel sheet?
Before we start, make sure that macros are enabled in your Excel. For that, Go to File -> Options -> Trust Center. In the Trust Center window, click on the Trust Center Settings present under Microsoft Excel Trust Center. See the following image for guidance.
Clicking on Trust Center Settings will open Trust Center setting window. Select the Macro Settings from the left panel and make sure all macros are enabled. Also, make sure that the Trust Access box is checked.
We just enabled all macros in Excel, now let’s create a macro using the script.
Creating A Excel Add-In For Geocoding Address To Coordinates
We will create a macro using this script which we will use to convert address to coordinates. For this purpose, we need to enable the Developer tab in MS Excel.
Step 1:
Go to File -> Options -> Customize Ribbon. Here, on the right side of the window, you can see all of the main tabs currently enabled on the Excel ribbon. Enable the Developer tab by checking the checkbox in front of it. Now, Developer tab will also be shown on the ribbon in Excel.
Step 2:
In the sheet, go to Developer tab and click on Visual Basic; this will open a Visual Basic window.
Step 3:
In the Visual Basic window, go to Insert tab and select Module; it will create a module.
Step 4:
In this module, paste the script that can be found here. Now, go to Tools tab and select References; it will bring a References pop-up.
Step 5:
In the References pop-up, locate the Microsoft XML v3.0; check it to enable and click OK .
Now, by clicking on Save button, save this module with *xlam extension. Name it whatever you want, just make sure the extension is *xlam (this is an Excel Addin extension) and save it somewhere you can remember; we will use it later in this tutorial.
At this point, we have successfully created an Excel Add-in that can convert address to coordinates. You can use this add-in in any MS Excel sheet for geocoding addresses by adding this in that sheet.
How To Use GeoCoordinates Add-in In Any Excel sheet?
To use this add-in, first of all, we have to add it to our Excel sheet.
Step 1:
Open an Excel sheet and go to File -> Options -> Add-Ins. At the bottom of this window, select the Excel Add-ins under Manage and click Go, an Add-Ins pop-up will appear on the screen.
Step 2:
In the Add-Ins pop-up, browse to the file you saved earlier with *xlam extension and select it. It will appear in the available Add-Ins list; check it to enable and click OK. The add-in is now successfully loaded in our Excel sheet. As you can see, the name of this add-in is GetCoordinates, which is defined in the script.
Now, to use this add-in in your sheet, create a Coordinates column next to the Addresses. To find the coordinates for an address, go to the Coordinates cell respective to that address cell and type =GetCoordinates(*address cell number*). Also, instead of typing all, you can select it from the drop-down as well.
Here, you can see a preview of how to find the coordinates for an address. Let’s say we needed to find the coordinates for the address in the cell A2, we have to enter this cell number is entered in the respective coordinates cell.
We can use the Excel feature to find the same for rest of the addresses. In case you are not familiar with that, just find the coordinates of very first entry and click on that coordinates cell; a boundary will appear around it. Click on the bottom right corner of that boundary and drag it all the way down to the last entry, it will do the same for rest of the addresses.
Closing Words:
This method of geocoding address to coordinates is a little bit difficult to set up, but once done, it can save you a lot of time. In some entries, you might get server limit exceeded message; just give that entry another try. It will give you the results; unless you have crossed your daily limit of 2500 requests.
