I wrote a tutorial about how to do geocoding with Microsoft Excel and Google Maps but saw few comments about the limitation which is 2500 addresses. Surfing the internet I found interesting number from Bing maps which allows you to perform 10000 addresses in total. If you need more than that then create a new MSN or Hotmail account and get the new Bing map API key.
So somebody has already created a excel template with all the required coding, you just have to create a Bing map API key.
1. Download the excel sheet or go to the http://excelgeocodingtool.com/ and download it.
2. Open the downloaded excel file and if it is open in protected view (highlight yellow bar on the top) then click enable editing.
3. After that process, enable macros.
4. In Excel 2013: Go to File -> Options -> on the left side, choose Trust Center -> click on Trust Center Settings and Macro Settings
5. In Excel 2007, 2010: Click on the Office button -> Excel Options -> Trust Center -> Trust Center Settings-> Macro Settings.
6. Then check the Enable all macros
Note: Make sure to disable macros once geocoding process is completed for the security purpose.
Below image is from Microsoft Excel 2013
7. To process the geocoding you have to generate Bing API key, go to Bing Maps API key. This will take you to login page, you can use your Hotmail or MSN account to login in. Once the initial process is completed you are ready to generate the key. To generate the key click on My account and then on My keys.
8. Then fill the value according to the nature of your project or anything that you like to fill up. Then click on Create button, which will take a while to display the key, see below image.
9. Go back to your open Geocoding Excel template and paste the generated code into the text box of Bings Map API.
10. Then go to Geocode sheet, add all your addresses under the Location column and press the Geocode Selected rows or Geocode all rows button. This will generate Latitude, Longitude and other info.
As mentioned above, you can create new MSN account to generate new API key when your 10000 limit is over. Hope you liked the tutorial and also, if you have any question please comment.
martin
11 Nov 2016not working i get a runtime error 9 subscript out of range at
‘set lat/long/confidence
latitude = geocodeData(0)
Kevin
8 Mar 2017Hi, thank you so much. very nice!
ryan
19 Mar 2017I have the latitude = geocodeData(0) error even though i’m using ur address. Why is this happening
Renell John Maglalang
30 Mar 2017Can you please send directly to my e-mail? IT blocked the website for download.
admin
2 Apr 2017Sorry what I have to send you, did not got you very well
Miguel A Chavez
12 May 2017Thank you so much for sharing . It works
admin
15 May 2017Thanks
Thomaz
3 Aug 2017not working i get a runtime error 9 subscript out of range
Thomaz
3 Aug 2017changed to excel 2016 and worked, thank you
vuongluu
11 Oct 2017Dear admin,
I love this tool but can you do reverse geocoding?
I have a data of Lat & Long and would like to reverse it to the address location , so as to get the Province, Ward, City etc.
Rob
19 Oct 2017The worksheet works really well, very easy to use and accurate. I should point out to others if you aren’t aware, this spreadsheet calls on the REST API which allows up to 125,000 billable calls per year at no charge, after that I’m not sure how much Microsoft charges per additional call. Details are in the link provided.
https://www.microsoft.com/en-us/maps/create-a-bing-maps-key
admin
21 Oct 2017Thank you
jens
20 Oct 2017Hi
This tool is amazing !!!! For me is works on
Win7 SP1 MS Office 365 Pro Excel 2016 ( Vers 1705)
Thanks heaps !!!
Cheeers, Jens
admin
21 Oct 2017Thank you
jojo
5 Nov 2017hello, i would like to use the excel geocoder but it doesn’t seem to work. i have enabled the macros (set it to medium security level). could it be a problem that i don’t have excel but only open office on my computer? sorry about this silly question! 🙂
FH
23 Jan 2018Worked like a charm!!! Thanksss 🙂
I had exactly 10,000 addresses to be geocoded! I am so glad i found your article!!
admin
24 Jan 2018Thank you:)
JK
4 Apr 2018Thanks for the coding & file. However failed to run by under
Win 7 + Excel 2013.
Anyone could guide on the Proxy? And will it possible on the “Reference – VBAProject” should tick anything to make it works?
Juliano
1 Aug 2018I need to encode addresses in Brazil, is it possible?
Sri
7 Aug 2018not working i get a runtime error 9 subscript out of range
John
8 May 2019Hi – I’m getting the Run-time error ‘9’ – is there a fix for this? Many thanks!
FIona FOley
3 Jan 2020Fabulous work…Love it…NOW to add the KM’s between 2 addresses. Will post this here is you like
joe
18 May 2022thanks, its working.
would you add some option to reverse geocode (lat, long) in to an address ?
SweScorpio
29 Feb 2024Is that file no longer available ?