Custom Function to Auto-Populate Latitude and Longitude in Excel (Updated 2021)

When analyzing real estate investments in Excel, it’s often necessary to include the latitude and longitude coordinates of the properties we’re modeling. To do this, most of us open up Google Maps, or some other mapping tool, and find the coordinates that way. Others use a latitude/longitude lookup tool such as latlong.net to find what they need.

However, these tools require leaving Excel, manually grabbing the coordinates, and then copying them back into Excel. While not overly time consuming, there is a more effective and accurate way to quickly grab a property’s coordinates without leaving Excel. In this post, I share some VBA code that you can add to your Excel file (together with a Google Geocoding API key) to automate the process of auto-populating latitude and longitude in Excel. Note that we have confirmed as of July 2021 that this function still works.

Note: Is working in VBA too daunting? Consider using our A.CRE Geocoding Excel Add-in to further simplify the process. Are you an Accelerator member? Download the Add-in free instantly.

 

Google Geocoding in Excel

Google has developed a service for converting addresses (like “555 Main St., Anywhere USA, CA”) into geographic coordinates (like latitude 36.411022 and longitude -120255489). The service, made possible by the Google Maps Geocoding API, is commonly used online to quickly convert physical addresses to coordinates.

What is less common, but just as easily done, is to use the geocoding API with a custom Excel function or via an Excel Add-In to convert a physical address to latitude and longitude coordinates instantly and without having to leave Excel. In this tutorial, I show you how to use VBA code to create a custom Excel function. If you’d rather use an Excel add-in, checkout our A.CRE Geocoding Excel Add-in.

Manually Add a Google Geocoding Custom Function via VBA

The first option for incorporating Google Geocoding into Excel, is to write a custom function that will take an address and instantly convert it to latitude and/or longitude coordinates using Google’s geocoding service.

Now if you’re unfamiliar with the concept of custom functions in Excel, a custom function acts like any other function in Excel (e.g. SUM, EDATE, VLOOKUP) only that it is specific to the workbook you create it in, and requires you to save the workbook as a macro-enabled file. This may be exactly what you want since, if you share the workbook, other users of the workbook will have access to the custom function.

However, if you’d like this function to be available every time you open up Excel, you might consider installing the A.CRE Geocoding Excel Add-in.

Adding the Google Geocoding custom function only takes a few minutes and the process is quite painless. I learned how to do this after reading a post on the subject, written by Christos Samaras, on his engineering blog (of all places!). I’ve recorded a video (see below) that walks you through the simple process of creating the function using Mr. Samaras’ VBA code. The video also explains how to get and insert your Google Geocoding API key.

To get the VBA Code used in this tutorial: Click Here

Once the custom function is in your model, using the function is simple:

1. In the cell of your choosing (e.g. cell B2), write a physical address in this order: STREET ADDRESS, CITY, STATE, ZIP CODE (example: 555 Main St, Anywhere USA, CA, 90229).
2. To get the latitude of the address in cell B2, use the formula = GetLatitude(B2)
3. To get the longitude of the address in cell B2, use the formula = GetLongitude(B2)
4. To get both the latitude and longitude of the address in cell B2, use the formula = GetCoordinates(B2)

Troubleshooting Issues

Here are the most commons issues that arise when manually using the Geocoding custom function:

1. Failure to Enable XML v3.0 library. In order to use this function you must enable the XML, v3.0 library from VBA editor. To do this, ‘Go to Tools -> References -> check the Microsoft XML, v3.0.
2. You haven’t inserted a proper API Key into the code. As of 2018, the code requires you to insert your own Google Maps Geocoding API key. And as of 2019, it’s necessary to attach billing information to your Google account and link that to your Google project. To get a free API key from Google, use this quick start guide provided by Google.
3. Getting a #VALUE, #NAME, or ‘Server denied the request’ errors. There are a handful of reasons for these errors but generally these errors are because either the function wasn’t setup correctly or the Google API key wasn’t setup correctly. My best advice if you’re struggling with one of these error is to:
   1. Follow the instructions to the T. I know that’s easier said than done, but if you’re not saving the file as a Macro-Enabled file, not turning on Microsoft XML 3.0, entering the API key in the wrong place, inadvertently deleting something from the VBA code, or not entering the address correctly, etc, than you will get an error.
   2. Make sure you’re running the most recent version of Excel. This code may work with older version of Excel, but I have not tested it on versions earlier than 2016.
   3. Make sure you have billing details on file with Google in order for your API key to work. While Google gives you a certain number of API requests for free, Google requires users have a credit card on file to avoid abuse of the API.
   4. Also here’s a quick tip. If you use the GETCOORDINATES() function, the VBA code has a few built-in custom error messages that will help you troubleshoot what you’re doing wrong. For instance, if you haven’t setup your API key correctly, the cell will output: “Invalid API Key”.

Alternative: Use the A.CRE Geocoding Excel Add-in

While writing a custom function is a viable solution for converting addresses to latitude and longitude coordinates, it has two disadvantages. First, it requires going into the VBA editor and manipulating code. That can be an intimidating task for some. The second issue with writing a custom function, is that it only exists in that one Excel workbook, which means if you want the function available beyond that Workbook you’ll need to create a custom Excel Add-in.

Unfortunately, creating a custom Excel add-in is not as simple as writing a custom function. So we’ve gone ahead and created a downloaded custom Excel add-in for this particular task. That add-in is called the A.CRE Geocoding Excel Add-in. You can learn more about the add-in and how to install and use it by clicking here.