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.

Note that the file is currently in beta, meaning it likely contains bugs. As bug reports and feature requests come in, we will release new versions of this tool.

A custom Excel add-in to make address-to-latitude-and-longitude conversion easy

Compatibility

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

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.


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 2020 – 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
  3. In the ‘Manage API Key’ section, click the key button
  4. A ‘Manage API Key’ dialog box will appear
  5. Copy your Google Geocoding API key and paste it into the ‘API Key’ box
  6. Click the ‘Set API Key’ button.
  7. Click ‘Exit’
  8. Your personal Google Geocoding API key has now been added to your personal copy of the A.CRE Geocoding Add-in

Activate and Deactivate the A.CRE Geocoding Add-in in Excel

To ensure that your Google Geocoding API key is not making requests unnecessarily, the A.CRE Geocoding 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
  2. In the ‘Addin Status’ section, click the check mark button
  3. The ‘Add-in Status’ 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, a ‘Disable’ 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

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?

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

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