Auto Populate Latitude and Longitude Function in Excel (NEW VERSION)

How to write a custom function that converts a physical address to latitude and longitude coordinates in Excel using Google's Geocoding API.

00:00 - Introduction
00:16 - Overview of Custom Function in Excel
00:47 - Accessing Developer Ribbon in Excel
01:09 - Opening Visual Basic for Applications
01:31 - Finding and Copying the Code
02:21 - Pasting the Code in Visual Basic
02:51 - Enabling Microsoft XML Version 3.0 Reference
03:18 - Requirement of Google API Key (2018 Update)
04:11 - How to Get Google Geocoding API Key
05:41 - Testing the Custom Function in Excel
06:05 - Conclusion and Final Thoughts

------------------------------------

My best advice for those struggling to get this to work is to:

A) 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 or inadvertently deleting something from the VBA code, or not entering the address correctly, etc, than you will get an error.

B) 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.

C) 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 (~2500/day last I checked), Google requires users have a credit card on file to avoid abuse of the API.

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".

Note that in the video, I'm using Excel 365 on Windows 10, and I have provided billing details to Google in order to secure the API.

[adventuresincre]

My best advice for those struggling to get this to work is to:

A) 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 or inadvertently deleting something from the VBA code, or not entering the address correctly, etc, than you will get an error.

B) 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.

C) 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 (~2500/day last I checked), Google requires users have a credit card on file to avoid abuse of the API.

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".

Note that in the video, I'm using Excel 365 on Windows 10, and I have provided billing details to Google in order to secure the API.

[chipymunk]

Holy Shnikeys... It worked... Dude, you have no idea what you have done for me... I am not a real estate person, but this is huge for me..

[pv]

Spencer you are an amazing person on this planet. This planet is proud of you. Thank you.

[sammweh]

I just tried this and it worked, thank you so much for the clear tutorial!

[cmsty]

Thank you! You have explained everything so clearly, wish all tutorials were like yours

[charisselavell]

Hi thanks for your tutorial. When I test it, I get Server denied the request. Any suggestions on what I can do?

[ElizabethSLazaro]

Definitely works with just city/state or city/country. No zip needed. But nice to have. Great!

[pdmarsh]

For the #VALUE errors - one more thing to check: make sure you have enabled the Google Maps "Geocoding API". There are a number of different Google Map API's and this code requires the Geocoding API to work (and valid API key, billing account, etc.)

[ciairmi]

Thank you so much! Even if Google has changed some features of the service, the vba does works great.

[MKdreambox]

Absolutely superb!! I just began working with GIS and this works like a charm! Thank you.

[Steve-hurk]

Does anyone know how to enable XML on Excel for Mac?

[taylorhenderson]

One note- I was having issues with the #Name error and realized that my address information wasn't entered correctly. Make sure your information is entered correctly: Street City Comma State Zip Code

Thanks for the awesome tutorial! Using this in Power BI!

[konstantinegorov]

thank you for the updated code. gets my project going again

[omairbinenam]

Will appreciate if reverse search and convert any given coordinate (lat/lon) into a complete address...

[blackwhite]

Hey Spencer! Thank you very much, it is a really good explained tutorial!

[richardhabbershaw]

Thanks, very handy tutorial and relatively easy to implement. One suggestion, you’ve created two individual functions for latitude and longitude each requiring an API call if called individually. Quick solution return the lat/lng as a string into a single variable and then break down in Excel rather than VB. Reduces calls to the API and hence saving time and money.

[AlexanderArndt]

I am using MAC OS Mojave Version 10.14.2 and the XML v3.0 library isn't shown at all...

[sumantra_sarkar]

Thanks. Do you have any video on the reverse please (i.e. latitude + longitude to an address ) please.

[fvpqyfu]

Throws value error. Tried everything in the comment section and troubleshooting section.

[ektasharma]

i want just automatically import excel data into system software ... could u help out