This tutorial explains how to reverse geocode coordinates to address in Excel. We will do this using an Excel add-in that will generate addresses corresponding to the lat long coordinates that you add in Excel sheet. Although, we have to create that add-in ourselves using this script. But, don’t worry, I will walk you through all the steps involved.
This script is a VBA (Visual Basic for Applications) one that can reverse geocode in bulk in Excel. It uses Google Geocoding API for reverse geocoding coordinates to address. Using this, you will be able to convert up to 2500 addresses daily.
In case, you are looking for geocoding address to coordinates in Excel, check out our tutorial How To Geocode Address To Lat Long In Excel.
Before You Start:
For using this script, you have to enable macros in Excel. Follow these simple instructions to do that:
Go to File -> Options -> Trust Center. In the window that opens up, click on the Trust Center Settings button present at the bottom right area.
A Trust Center window will open on your screen. Go to Macro Settings present in the left-side panel and select the Enable all macros radio button there. Also, check the Trust Access checkbox under Developer Macro Settings.
Now, macros are enabled in Excel. So, let’s start the step by step tutorial for reverse geocoding coordinates in bulk using Excel.
How To Reverse Geocode Coordinates To Address In Excel?
This tutorial is divided into two parts:
- Creating An Excel Add-In For Reverse Geocoding Coordinates To Address.
- Finally, learn How To Use ReverseGeocode Add-in In Any Excel sheet?
How to Create Excel Add-In For Geocoding Address To Coordinates?
You need to enable the Developer tab in Excel for this purpose. Below are the steps to do so.
Step 1:
Open Excel and select a blank workbook. Go to File -> Options -> Customize Ribbon. Here, mark the checkbox in front of Developer on the right side of the window under Main Tabs, and click OK; it will enable Developer tab in Excel.
Step 2:
Now, come back to the sheet, you will see a Developer tab on the ribbon. Click on Developer tab -> Visual Basic, a Visual Basic IDE window will open.
Step 3:
Now you have to go to the Insert tab and click on the Module option. This will create a new module for you in the Visual Basic IDE.
Step 4:
Now in this new created module you need to paste the script code which you can find here. After pasting the script, head on over to the Tools tab -> References.
Step 5:
A References window will open up, find Microsoft XML v6.0; enable it by checking the checkbox in front of it and click the OK button.
Now, click on the save button in Visual Basic IDE to save this module. Select a location that you can easily remember because we will need this later. You can name this whatever you like, and make sure that the file type is *xlam (this is an Excel Addin extension).
An Excel Add-in to convert coordinates to address is created successfully at this point. This add-in can be used in an Excel sheet to reverse geocode coordinates to address.
How To Use This ReverseGeocode Add-in In Excel?
With the help of following steps, you can use this add-in in an Excel sheet for reverse geocoding.
Step 1:
You need to add this add-in in your Excel sheet where you want to use it. Open a new Excel sheet or another Excel sheet where you want to reverse geocode coordinates to address. Now, go to File -> Options. In the left side panel, click on Add-ins. Under Add-ins settings, select Excel Add-ins under Manage and click Go; it will open an Add-Ins pop-up.
Step 2:
In this Add-Ins window, browse and select the *xlam file that you saved. Now, that *xlam add-in will be seen in the Add-Ins list with Reversegeocoding name (this name is defined in the script); enable it by checking the box in front of it.
You will notice that the reverse geocoding add-in is now being displayed in your MS Excel sheet.
Step 3:
For using this reverse geocoding add-in in your excel sheet, create an Address column next to the Latitude and Longitude column. To find the address for a latitude and longitude entry, go to the Address cell respective to that coordinates entry and type =ReverseGeocode(*latitude cell number, longitude cell number*).
Here is an example to find the address for geographic coordinates. Let’s say we need to find the address for the first entry in the following image. We type =ReverseGeocode(A2,B2). Here, A2 represents the Latitude and B2 represents the Longitude. Excel sheet uses color to highlight these.
As soon as you hit enter after entering these details, the respective address will pop up right into the address cell.
You don’t have to specify the inputs to the add-in for each entry, instead, you can use the Excel drag feature to find the addresses for the rest of the entries.
Well, if you are not familiar with the drag feature; find the address for the topmost entry first. Now, click on the bottom right corner of the generated address cell and drag it all the way down to the last entry. It will generate the addresses for the rest of the coordinates entries.
Closing Words:
In this method of reverse geocode coordinates to address, creating the add-in part is a little tricky, but by properly following the steps, it can be done easily. Once it’s done, the process of using it is pretty easy and saves a lot of time by auto-converting coordinates to address in bulk.
