When you have the latitude & longitude (lat, lon) of a location and you want to turn them into a readable address (city, street, postal code, etc.), the process is called reverse geocoding. In Excel, there’s no built-in option to directly perform it.
However, Excel’s WEBSERVICE and FILTERXML functions query a free online API (a tool that provides data) directly from your spreadsheet to give you an address. We’ll use the OpenStreetMap Nomination API which is completely free to use.
➤ Put the latitudes and longitudes in two different columns (Columns C and D). In an adjacent empty cell where you want the address, enter:
=IFERROR(FILTERXML(WEBSERVICE(“https://nominatim.openstreetmap.org/reverse?lat=” & C2 & “&lon=” & D2 & “&format=xml”), “//reversegeocode/result”), “Address Not Found”)
➤ Replace C2 and D2 with the first cells containing the coordinates in your dataset. If the address isn’t found, the formula returns Address Not Found. Change the text if needed.
➤ Press Enter and drag the formula down to get all the addresses from the remaining cells.
This article covers all the methods for performing reverse geocoding in Excel using Excel functions and VBA coding.
Performing Reverse Geocoding with a Native Excel Formula
In our sample dataset, we have columns for Location Type, Yearly Visitors, Latitude, and Longitude. We’ll put the location in the Full Address column (Column E).
Here, we’ll use the OpenStreetMap Nomination API to fetch the necessary data. While it’s free, you can’t use it for bulk data and must avoid rapid requests as it might overload their service. In Excel 2013 and later versions, you can use the WEBSERVICE function to fetch data (in XML format) from the Internet using the URL you specify.
After that, FILTERXML extracts only the address from the XML data format. Below are the steps:
➤ In an empty cell where you want the address (E2), enter the following formula:
=IFERROR(FILTERXML(WEBSERVICE("https://nominatim.openstreetmap.org/reverse?lat=" & C2 & "&lon=" & D2 & "&format=xml"), "//reversegeocode/result"), "Address Not Found")
➤ Here, the URL https://nominatim.openstreetmap.org/… is the address of the free service we’re using. Cell C2 contains the latitude and D2 has the longitude. Replace the cell references according to your dataset. You can also change the Address Not Found that appears when the map fails to recognize the coordinates.
➤ Press Enter and drag the formula down. Excel will take a few seconds to give you all the addresses, including the Place, Street, City, County, State, Postal Code, and Country. For bulk extraction, use small batches in intervals.
➤ If Excel returns Address Not Found as shown in the image above, delete the cell content and drag the formula again to get the correct address.
Customizing a User-Defined Function (UDF) with VBA
With VBA coding, you can customize a UDF and use it as a formula to get addressees from coordinates. However, this method is also suitable for small batches of coordinates, not for bulk use. Here’s how:
➤ Right-click on the sheet name and select View Code from the menu.
➤ In the VBA Editor, close the current module using the cross sign(x) as shown in the picture below:
➤ Click on the Insert tab and select Module.
➤ As the new module box appears, paste the following code in it:
Function GEOCODEREVERSE(latitude As Double, longitude As Double) As String
On Error GoTo ErrorHandler
Dim http As Object
Dim xml As Object
Dim URL As String
Dim resultNode As Object
Set http = CreateObject("MSXML2.XMLHTTP")
Set xml = CreateObject("MSXML2.DOMDocument")
URL = "https://nominatim.openstreetmap.org/reverse?lat=" & CStr(latitude) & _
"&lon=" & CStr(longitude) & "&format=xml&addressdetails=1"
http.Open "GET", URL, False
http.setRequestHeader "User-Agent", "ExcelVBA-Geocoder"
http.send
xml.LoadXML http.responseText
' Try to get <result> node (the full address)
Set resultNode = xml.SelectSingleNode("//result")
If Not resultNode Is Nothing Then
GEOCODEREVERSE = resultNode.Text
Else
GEOCODEREVERSE = "Address not found"
End If
Exit Function
ErrorHandler:
GEOCODEREVERSE = "Error: " & Err.Description
End Function
Sub ReverseGeocoding()
End Sub
➤ Press the Tools tab and click on References.
➤ In the References – VBAProject box, scroll down and check the Microsoft XML, v6.0 box and click Ok.
➤ Now, go back to the Excel tab, click on the File tab, and go to Save As. Navigate to the correct location on your device to save the file. Click on the Save as Type drop-down.
➤ Select Excel Macro-Enabled Workbook, and press Save.
➤ In the Excel tab, insert the following function in an empty cell where you want the address:
=GEOCODEREVERSE(C2, D2)
➤ Replace C2 and D2 with the cell references containing the latitude and longitude.
➤ Press Enter and drag the formula down. As the function processes one geocode at a time, Excel might freeze for a few seconds before giving the addresses.
Frequently Asked Questions
How to get a Google API key for reverse geocoding?
Go to the Google Cloud Platform (GCP) website and log in using your Google account. Click the Project drop-down at the top >> New Project >> Give it a name (e.g., Excel Geocoding). In the left menu, select APIs & Services >> Library >> Search for Geocoding API >> click Enable.
Go to APIs & Services >> Credentials >> Click + Create Credentials >> API key. Copy the key that appears and that’s your personal access key. You can now use this key in Excel Power Query or VBA to do reverse geocoding.
Can I use Excel as an add-in for reverse geocoding?
Yes, you can use Excel add-ins to turn latitudes and longitudes into addresses, but this might require signing up for the platforms, using an API key, or purchasing a package. To add one, go to the Home tab and click on Add-In. Use the search bar to look for your preferred add-in. Some popular options include Maps for Excel, Bing Maps for Excel, CDXZipStream, EasyMapMaker, etc.
How to convert Excel data to Geography?
In Excel 365 and 2021+, enter geographic names (e.g., New York, California, Paris) in a column and select those cells. Go to the Data tab >> Data Types group >> Geography. Excel will now convert them into linked Geography data types (you’ll see a small map icon). Click the icon and a Data Card appears with details (population, area, leader, etc.).
Concluding Words
When using a free geocoding source, you must be patient with your requests as they have time limitations. Apart from the methods mentioned above, there are many third-party tools available with paid versions for bulk conversion. If you have a Google API key, switch to Power Query or VBA as both can read JSON.
