Spencer Burton
, , ,

A.CRE Geocoding Excel Add-in to Auto-Populate Latitude and Longitude in Excel (Updated Jun 2024)

The A.CRE Geocoding Excel Add-in uses the Google Geocoding API to automatically convert a given address to latitude and longitude coordinates, or to convert a given latitude and longitude coordinate to an address. The add-in makes auto-populating latitude and longitude (or address) in Excel easy. It includes various features to help add or change the API key and to cut down on unnecessary API requests.

This Geocoding Excel add-in is a simplified version of our comprehensive Excel 4 CRE addin. The Excel 4 CRE add-in includes numerous features specific to real estate professionals, including the features contained in this auto-populate latitude and longitude add-in.

  • Click here to view and download the more comprehensive (and free) ‘Excel 4 CRE’ add-in that contains this auto=populate lat/long feature.

In version 0.3 of this add-in, we added the option to automatically convert all latitude/longitude functions to values. This will help speed up your workbooks that use the FINDCOORDINATES(), FINDLATITUDE(), FINDLONGITUDE(), and FINDADDRESS() functions, as well as limit the number of API pulls you have in existing workbooks that use the functions.

For instructions on installing the add-in, click here.

Are you an A.CRE Accelerator member? The Excel 4 CRE add-in (which includes this lat/long feature) is automatically added to your ‘My Downloads’ page upon purchase. Not yet an Accelerator member? Consider joining the real estate financial modeling training program used by top real estate companies and elite universities to train the next generation of CRE professionals.

Compatibility

  • Excel 2013 for PC and newer (Tested)
  • Excel for PC older than 2013 (Not Tested)
  • NOT COMPATIBLE with Excel for Mac

Download the A.CRE Geocoding Excel Add-In

To make this tool accessible to everyone, it is offered on a “Pay What You’re Able” basis with no minimum (enter $0 if you’d like) or maximum (your support helps keep the content coming – typical Excel add-ins sell for $25 – $100+ per license). Just enter a price together with an email address to send the download link to, and then click ‘Continue’.

We regularly update the model (see version notes). Paid contributors to the model receive a new download link via email each time the model is updated.

Proceed to Download Page

Installation Instructions – A.CRE Geocoding Excel Add-In

The first step to auto-populating latitude and longitude (or address) from a given address (or set of coordinates) in Excel, is to install the A.CRE Geocoding Add-in. Follow these instructions to install the add-in.

  1. Open a blank Excel workbook
  2. Go to File>Options
  3. In the ‘Excel Options’ dialog box, select ‘Add-ins’
  4. Next to ‘Manage:’, ensure the drop-down is set to ‘Excel Add-ins’ and then click ‘Go…’
  5. In the ‘Add-ins’ dialog box click ‘Browse…’
  6. A ‘Browse’ dialog box will appear, with the default Excel add-ins folder shown
  7. Leave the ‘Browse’ dialog box open, and open the A.CRE Geocoding Add-in zip folder containing the A.CRE Geocoding Add-in .xlam file (i.e. Microsoft Excel Add-in file).
  8. Drag and drop the A.CRE Geocoding Add-in .xlam file from the zip folder to the ‘Browse’ dialog box, such that a copy of the file now resides in the default Excel add-ins folder
  9. Right-click on the A.CRE Geocoding Add-in .xlam file newly added to the default Excel add-ins folder, and select ‘Properties’
  10. Within the file properties dialog box under the ‘General’ tab, look to see if there’s a Security setting at the bottom that reads: “This file came from another computer…” and if so, check the ‘Unblock’ box.
  11. If/when the ‘Unblock’ box is checked, click ‘OK’ in the file properties dialog box
  12. In the ‘Browse’ dialog box, make sure the ‘A.CRE Geocoding Add-in file is selected and click ‘OK’
  13. In the ‘Add-ins’ dialog box, make sure the ‘A.Cre Geocoding Add-in’ is checked and then click ‘OK’
  14. If a new ‘GeoCode’ ribbon tab appears (usually to the right of the Help ribbon tab), then the add-in was installed successfully
  15. Close Excel

Using the A.CRE Geocoding Add-in

Once you’ve installed the Add-in, you’ll need an active Google Geocoding API key for the add-in to function properly. That API key is free to create, and usage is also free within certain limits (as of 2023 – see link below about Google’s usage and billing). In this section, we’ll walk you through getting a Google Geocoding API key.

An Important Note About the Google Geocoding API Key

This Excel add-in takes an address (or coordinates), and converts it to latitude/longitude coordinates (or an address). The work of converting to coordinates or an address is done by Google Map’s geocoding service. Thus, the A.CRE Geocoding Add-in serves as the link between Excel and Google Map’s geocoding service to instantly convert an address to coordinates (and visa versa) in Excel.

So for this Add-in to work, you must have a Google Geocoding API key. A Google Geocoding API key is a unique code that that allows your computer to communicate directly with Google’s geocoding services. Google also uses the key to log the number of times you use Google’s geocoding services.

As of Oct 2020, Google charges $5.00 per 1,000 requests (1/2 of one cent USD per request) for each use of its API but then provides an ongoing free monthly credit of $200. Effectively, this means you can use the service free so long as you don’t use more than $200 worth of API requests per month. You can also set limits on the number of requests your API key can make in a given month. To learn more about Google’s geocoding pricing visit:

https://developers.google.com/maps/documentation/geocoding/usage-and-billing

Getting a Google Geocoding API Key Via the Google Maps Platform and Google Cloud console

To get your own Google Geocoding API Key, you need to do the following:

Note: Check out Google’s Getting Start Guide

1. Create a Google billing account

The first step to getting a Google Geocoding API Key is to create a Google billing account. While it’s unlikely you’ll ever use the API key enough in a month to actually be charged anything, Google nonetheless requires that you have billing details on file to ensure you don’t overuse the API. Note that once you create your API key, you can add restrictions so that the API key never exceeds the free threshold available to you.

2. Create a project

Once you’ve created a Google billing account, you next need to create a “project” that the API key you will be creating is a part of. To attach a billing account and then create project:

3. Enable the Geocoding API

Next, you’ll need to enable the Geocoding API within the project you created.

To enable APIs, check out the following video. At the ~1:00 mark in the video, rather than selecting the ‘Places API’ as shown in the video, instead select the ‘Geocoding API’. Once you’ve enabled the Geocoding API, you can create an API key.

4. Get, add, and restrict an API key

Once a Google billing account has been assigned to an active project, and the Geocoding API has been enabled, you then must get an API key. This API key is a unique string of characters that allows Google to determine that you’re the one requesting access to the Geocoding platform and log how many requests you’ve made in a given period. This API key will be added to your A.CRE Geocoding Excel Add-in (instructions to follow), which will allow the add-in to work properly.

In terms of restrictions. Leave the ‘Application restrictions’ set to None (i.e. you can ignore from 1:45 – 3:35 of the video). Under ‘Restrict Key’, select the ‘Geocoding API’ such that the API key is restricted to using that API.

5. Set Usage Quotas

To ensure that you don’t overuse your API key, and thus exceed your free allotment of uses each month, allow us to offer a few suggestions to keep your Google Geocoding cost to $0.

  1. Never share the API key with anyone. This key is unique to your account. If the key is used excessively and usage quotas have not been set, you will be charged for that use.
  2. Occasionally change your API key. The API key is like a password. To ensure it doesn’t get into the wrong hands, it’s good practice to periodically delete an existing key and replace it with a new key.
  3. Set usage quotas. Google allows you set usage quotas so that a key is not used more than you’d like. The below video shows you how to do that. I personally set my quota to 500 requests per month (well below the current threshold for usage to be free), as I rarely pull more than 500 latitude/longitude coordinates per month. But set the quota that’s right for you.

Add the API Key to the A.CRE Geocoding Add-in in Excel

Once you have a valid Google Geocoding API key, you need to add that key to the A.CRE Geocoding Add-in in Excel. The latest version of the add-in makes this step really easy. Here’s how you add your key:

  1. Once you’ve created a Google Geocoding API Key, open Excel with the A.CRE Geocoding Add-in installed
  2. Click the ‘GeoCode’ ribbon tab (or 4-CRE ribbon tab in the Excel 4 CRE add-in)
  3. In the ‘Manage API Key’ (‘Geocoding Lat/Long’ in the Excel 4 CRE add-in) section, click the key button (‘Geocoding Settings’ in the Excel 4 CRE add-in)
  4. A ‘Manage API Key’ (or ‘Geocoding Settings’) dialog box will appear
  5. Copy your Google Geocoding API key and paste it into the ‘Google API Key’ box
  6. Click the ‘Set API Key’ button.
  7. Your personal Google Geocoding API key has now been added to your personal copy of the A.CRE Geocoding Add-in

Activate and Deactivate this Geocoding feature in Excel

To ensure that your Google Geocoding API key is not making requests unnecessarily, the Add-in includes the feature to activate and deactivate the API feature at any time.

To activate or deactivate the API feature:

  1. Click the ‘GeoCode’ ribbon tab (or 4-CRE ribbon tab in the Excel 4 CRE add-in)
  2. In the ‘Addin Status’ section (‘Geocoding Lat/Long’ in the Excel 4 CRE add-in) section, click the check mark button (‘Geocoding Settings’ in the Excel 4 CRE add-in)
  3. The ‘Add-in Status’ (or ‘Geocoding Settings’) dialog box will appear
  4. If the API feature is currently active, a ‘Disable’ button will be visible. If the API feature is currently deactivated, an ‘Enable’ button will be visible
  5. Simply click ‘Enable’ or ‘Disable’ to activate or deactivate the API feature.
  6. Once your selection has been made, click ‘Exit’

Auto-populate Latitude and Longitude Using an Address

Once you’ve added a working Google Geocoding API key to your copy of the A.CRE Geocoding Add-in and activated the API feature, use the following custom functions to auto-populate latitude and longitude from an address.

FINDLATITUDE()

Use the =FINDLATITUDE([address]) function to find the latitude of a given address. So, for instance, if you’re like to find the latitude of the Empire State Building:

  1. Enter the address for the Empire State Building in a given cell (e.g. cell B2)
  2. In a different cell from where the address was entered, write the following formula:

=FINDLATITUDE(B2)

  1. Hit enter and the A.CRE Geocoding Add-in will use the Google Geocoding tool, via your Google Geocoding API, to lookup the latitude and return that value into the cell

FINDLONGITUDE()

Use the =FINDLONGITUDE([address]) function to find the longitude of a given address. So, for instance, if you’re like to find the longitude of the Empire State Building:

  1. Enter the address for the Empire State Building in a given cell (e.g. cell B2)
  2. In a different cell from where the address was entered, write the following formula:

=FINDLONGITUDE(B2)

  1. Hit enter and the A.CRE Geocoding Add-in will use the Google Geocoding tool, via your Google Geocoding API, to lookup the longitude and return that value into the cell

FINDCOORDINATES()

Use the =FINDCOORDINATES([address]) function to find the latitude AND longitude of a given address. So, for instance, if you’re like to find the latitude and longtidue of the Empire State Building:

  1. Enter the address for the Empire State Building in a given cell (e.g. cell B2)
  2. In a different cell from where the address was entered, write the following formula:

=FINDCOORDINATES(B2)

  1. Hit enter and the A.CRE Geocoding Add-in will use the Google Geocoding tool, via your Google Geocoding API, to lookup the latitude and longitude and return that value (in that order) into the cell

Auto-populate and Address using Latitude and Longitude

In the same way that you can take an address and find the latitude and longitude, you can also take the latitude and longitude and find the address. To do this use the FINDADDRESS() custom function.

FINDADDRESS()

Use the =FINDADDRESS([latitude],[longitude]) function to find the address of a given latitude and longitude. So, for instance, if you’re like to find the address of the Empire State Building:

  1. Enter the latitude of the Empire State Building in a given cell (e.g. cell B2)
  2. Enter the latitude of the Empire State Building in a separate cell (e.g. cell B3)
  3. In a different cell from where the latitude and longitude were entered, write the following formula:

=FINDADDRESS(B2,B3)

  1. Make sure you enter latitude first, followed by a comma, followed by longitude second
  2. Hit enter and the A.CRE Geocoding Add-in will use the Google Geocoding tool, via your Google Geocoding API, to lookup the address and return that value into the cell

Using the Clear Formula Feature and Other Changes In Version 0.3

In mid-2021, we updated the A.CRE Geocoding Excel Add-in to include several changes including the addition of a new Clear Formula feature.

Assuming you’ve installed v0.3 of the add-in, simply click the ‘Clear Formula’ button under the ‘GeoCode’ ribbon and any formulas you’ve written with the functions from this add-in (e.g. FINDCOORDINATES()) will automatically be removed and replaced with the values from that cell. This way, you can avoid excessive API pulls from Google.

Here’s a short video describing the changes in the latest version of this add-in:

Uninstallation Instructions – A.CRE Geocoding Excel Add-In

If you’d like to remove the Add-in from your version of Excel, refer to the following steps:

  1. Open a blank Excel workbook
  2. Go to File>Options
  3. In the ‘Excel Options’ dialog box, select ‘Add-ins’
  4. Next to ‘Manage:’,ensure the drop-down is set to ‘Excel Add-ins’ and then click ‘Go…’
  5. Uncheck the box next to ‘A.Cre Geocoding Add-in’
  6. Click ‘OK’
  7. Hover over the ‘GeoCode’ ribbon tab
  8. Right-click and select ‘Customize Ribbon’
  9. In the list on the right-side of the ‘Excel Options>Customize Ribbon’ dialog box, find the ‘GeoCode (Custom)’ item
  10. Hover over the ‘GeoCode (Custom)’ item and right-click
  11. Select ‘Remove’
  12. Click ‘OK’
  13. The add-in has been successfully uninstalled
  14. Close Excel

Having Issues with the Add-in?

  • Receiving a ‘REQUEST_DENIED’ error in Excel when attempting to use the add-in? This is due to an issue with your Google API key. It’s similar to a ‘incorrect password’ error when attempting to login to a website. Effectively, Google doesn’t recognize the API key or the key was not setup correctly. Return to the ‘Using the A.CRE Geocoding Add-in’ section above, paying special attention to the Google instructional videos in items 1 through 5.
  • Are cells returning blank when using any of the A.CRE Geocoding Add-in functions? This occurs when the add-in has been disabled. To enable the add-in, go to the ‘GeoCode’ ribbon and click the ‘Addin Status’ button. Then click ‘Enable’.

Not finding a solution to your issue, have a bug to report, or a feature you’d like added? Click here to contact us.

Version Notes

v0.3

  • Added Option to ‘convert to values’.
  • Added a button in GeoCode ribbon that when clicked will ‘Convert All Lat/Long Formulas to Values’
  • Added clickable hyperlink in the ‘Manage API Key’ dialog box that takes user to Google’s instructions for getting API key.
  • Converted urls in ‘Manage API Key’ and ‘Addin Status’ toolboxes to clickable hyperlinks
  • Renamed ‘Addin Status’ ribbon item to ‘Add-in Status’

beta v0.2

  • Renamed ‘Set API Key ribbon section to ‘Manage API Key’
  • Added ‘Readme’ file to download
  • Added links to instructions in the dialog boxes

beta v0.1

  • Initial release
by
You might also like
Using ChatGPT to Create Excel Logic #2 – Annual to Monthly Growth
Hide and Unhide Tabs using Drop-down Menus in Excel (Updated Apr 2024)
Create Printable and PDF Reports in Excel for Real Estate Financial Modeling
How to Create Dynamic, In-Cell Buttons and Toggles in Microsoft Excel
Using OpenAI’s ChatGPT to Create an Excel Macro for a Real Estate Model
SUMPRODUCT Weighted AverageUsing SUMPRODUCT to Calculate Weighted Average in Real Estate (Updated Aug 2024)