Export Excel Data to Google Maps!
Wouldn’t it be nice if you could export an Excel list of addresses to Google Earth and Google Maps? Thankfully, there are several ways to do this but the only question you need to ask yourself is, “How secure does my data need to be?”
Although Google Maps gives you the option to remove your saved maps from search results, a public URL of your map still exists and can be accessed by anyone with that URL. If in doubt, keep your map’s URL to yourself or use Google Earth instead.
Mapping Excel Data Using VBA
Process Trends created an Excel spreadsheet that incorporates the work of Juice Analytics and simon_a to geocode addresses and prepare them for Google Earth and Google Maps. To begin, download the MapExcelData.xls file and follow along.
**Attention Excel 2004 for Mac users, the MapExcelData.xls spreadsheet will not work due to the missing VBA Split function. Unfortunately, you’ll have to run MapExcelData.xls on a Windows version of Excel.
Step 1: With macros enabled, open the MapExcelData.xls spreadsheet and go to the KML_details tab. Change the document name in cell C3 to whatever you wish, and update the file path in cell C2 to indicate the name and save location of your KML file.
For example, if you want the file to be named digdug.kml and saved to your desktop, the file path will look something like this:
Step 2: Go to the data tab and starting at row 6, paste your address data in columns D through I. Leave columns A, B, and C blank since the macro will find this information for you.
Step 3: Select the rows you would like geocoded, and click the Geocode button at the top of the spreadsheet. The latitude and longitude values will begin to fill columns A, B, and C.
Step 4: Once geocoding is done, click the Gen KML File button and your KML file will be created. If you have Google Earth installed, then click the Run Google Earth button in the spreadsheet or double-click the newly generated KML file to see your addresses plotted on the globe.
In the interest of time, I’ve created a pdf that shows you how to export Google Earth locations to Google Maps if needed, or…
Bonus Tip: You can open your KML file in Google Maps without the help of Google Earth. First, upload your generated KML file to a public web space like your blog or Dropbox public folder. Next, copy and paste the URL of the file to the maps.google.com search field—click the Search Maps button and you’re done.
Feel free to give it a try with this URL:
http://theclosetentrepreneur.com/wp-content/uploads/2009/02/digdug.kml
Once your addresses are in Google Maps, you can click the Save to My Maps link and have them available whenever you need them.
Other Ways To Map Excel Data
If you don’t want to use the MapExcelData.xls spreadsheet, then batchgeocode.com allows you to create a KML file from your Excel data using their online form.
Another option is to use Dabble DB, an amazing online database tool that can generate KML files for Google Earth. Unfortunately, your data is public when using their free plan so either pay for a plan if you need privacy, or take advantage of the free 1 month private trial.
In Conclusion…
I’m hoping you can make use of this blog post, but If you’ve read this far and feel that you have no use for this information, then consider…
- Creating a map of your delivery locations
- Mapping out a road trip
- Creating a map of foreclosures or homes for sale
- Map your favorite art galleries or hole in the wall restaurants
- Map local coffee shops, cafés, and coworking spaces that offer free Wi-Fi
- Create a map of local arcades that have coin-operated Galaga machines
- Create a map of local restaurants that offer 24 hour service
- et cetera…
See any uses for exporting Excel data to Google Maps that I may have missed? If so, feel free to leave a comment!
Sincerely,
The Closet Entrepreneur
Post Scriptum: A big Thank You goes out to the guys at REsolved LLC for the tips on their Get Your Data on Google Maps For Free video!
» This entry was filed under Excel and tagged with: Advice, tips, Tutorials
81 Comments
Jorge
Por favor como puedo hacer para exportar coordenadas de google earth a excel
Jun 2nd, 2011
Dennis
Dear Sir,
Does the programm also work with other countries, like the Netherlands?
I have tried this but it doesn’t work.
Can you please help me?
Thanks,
Best regards,
Dennis
Jun 6th, 2011
Chrissy
Great information – thank you!
Jun 6th, 2011
Matteo
Great work, you saved hours and hours of work… and now my marketing collegues can work directly on the excel file and can generate the updated kml file with a click.
Thanks.
Jun 22nd, 2011
elma
@laurent, @Frederik
I too had that issue.
I still haven’t managed to get it to work from the ‘Run Google Earth’ button, but have managed to open the kml file from Google Earth itself (ensuring that the filepath in Step 1 ends in .kml)
@Tim – would you be able to share your knowledge with regards pin style and colour etc?
Jul 5th, 2011
Nick
It wouldn’t work when I intered C:\filename in cell C2. Replaced C: with D: and it works fine. Problably a permission issue.
When using it for other countries than the US, I’ve found it to be useful to enter the country in the “State” column.
The only problem left is the format of the longitude and latitude. It exports the longitude and latitude using a , (e.g., 6,1) rather than a . (6.1). A solution to this would be very much appreciated, since I now still have to manually change all of this.
Aug 16th, 2011
Mani
Tomas,
Do you know of any way of going the opposite direction. I’ve made a map on google maps of 150+locations that I would like to export to an excel file. Is there any way of doing this?
Thanks!
Aug 22nd, 2011
Mani
kindly reply me as soon as possible
it will be much helpful if u replied soon..thanks…
Aug 23rd, 2011
Christian
I downloaded this excel file and was able to get the Yahoo geocoding to work but it won’t create an .kml file. I tried different folders and filenames to no avail – when i search my computer for a .kml file I get nothing.
The only thing I can think of that would still be a problem is that I’m using office 2010 and it looks like this file was created in Excel 2003. I tried it in compatibility mode, and also tried updating the file to 2010 and neither have worked so far. Does anyone else have this same issue and know of a fix?
Thanks
Christian
Aug 25th, 2011
Bruce
Here’s another way to map data directly from excel into yahoo, google, ovi and bing maps
http://ramblings.mcpher.com/Home/excelquirks/getmaps
Bruce
Sep 8th, 2011
albert
I tried it and it worked perfectly. A very useful tool.
Now I tried to export the data to an Arcmap project but I don’t know the spatial reference of the cordinates and I can’t see it. Is there an option to check it or can I get the data from yahoo server? I can’t find it!
Thanks!!
Sep 14th, 2011
Jonathan
I have been using this tool for about 2 weeks and everything has been working perfectly until today! When I input the address and press the Geocode button all I get is “not found” for the Lat, Long, and Percision fields. It will still let you create the KML file however all the points will be put in the same but random location on Google Earth. Any suggestions??
Sep 20th, 2011
albert
Is the yahoo service down? For how long? Is not working anymore..
Sep 21st, 2011
Jon C.
I would like to know whether it is possible to retrieve a map of locations and lat/long information based on addresses within Malta.
This is part of an academic project where I would then seek to compare parking spaces and bus routes within the Maltese Islands, and, provide travelling information such as:
distance between two points (on the road)
fuel consumed based on a price tariff
bus routes available within the area
Sep 25th, 2011
david
Hi,
for certain issues you can also use my tool:
http://winowin.de/product.php?pf=windows&prod_id=56628&xsearch=kml
(Demo is free)
Greets
David
Oct 3rd, 2011
Josh
Hi,
I have used this excel geocode in the past and now that I have office 10, geocode can not find long/lats for my addresses… I have all macros enabled and now wondering if it has to do the firewall or so on. Any info would be a great help. Thanks
Oct 6th, 2011
Tom
I am having the same problem as Jonathan but this is the first time I used it.
Oct 7th, 2011
albert
It’s not working anymore Jonathan. I also get the “not found” because the yahoo api is no longer functional. It should be a way to change the macro to use another aplication such Google Api but I don’t know how. If somebody can change it please, let me know.
Here there is another tool to geocode from excel
http://ramblings.mcpher.com/Home/excelquirks/getmaps
Oct 13th, 2011
bruce
Although Yahoo Maps was supposed to close last month, I just tried
http://ramblings.mcpher.com/Home/excelquirks/getmaps
which can geocode or map using all of Yahoo, Bing, Ovi, or Google- and the Yahoo option is still working as of today.
Bruce
Oct 13th, 2011
norma peralta
Was wondering if you could help me with the code changes/modifications needed so i can add different pin styles and colors.
Thanks you! I love this tool BTW!
Norma
Oct 20th, 2011
Samarth
TOMAS: Thanks for the great post. I’m using 64 bit OS as well as Office. and it gives me an error indicating that i need to change some things in the VBA code to make it work. the code in mClipboard is red so I think I need to change something there. Can you please guide me?
Oct 31st, 2011
Eddy
Hi, thanks for that however i’ve got a question
Does this only work in USA or where i am too – New Zealand ?
It doesn’t seem to be getting the Lat. & Long. any reason why ?
Thanks!
Feb 20th, 2012
alberto
Hello, I have an excel dataset with origin and destination of trips in two columns. I would like to add a column with the distance between the two points. Is there a way to get google maps to calculate these withouth me having to do it manually for all the trips (they are more than 1,000)
thank you very much
Alberto
Mar 14th, 2012
mucrick
I’ve been using MapExcelData.xls with great success for a couple years. I’d like to know what I need to edit in the file that will allow me to set the height and width of the pop-up balloon.
I welcome this groups’ input.
Regards,
John
Apr 1st, 2012
Drew
I’ve spent tons of time looking around for the best solution to this. Batchgeo was the best for a while since it made it simple to create a quick map of my data. Or TotallyAwesomeMapping.com was perfect for creating Google Earth KML (similar to this post above).
I’ve since moved on to http://Topo.ly. They not only build the map from Excel data, but then they let me do all kinds of analysis on top of it. I can add multiple layers of data on my map and give them different icons for each data set. Then I can filter each layer with values from any of the columns from my spreadsheet.
You can also build heat maps so that I can show high performing zip codes or counties based on my spreadsheet data.
Anyway, definitely worth a look. Thanks for the post, Tomas!
Apr 26th, 2012
Mitch
Maybe someone can help me with this question. How come when I put in my address on the “data” tab and try to geocode them for the latitudes/longitudes it says “not found”. I would love to be able to geocode my data in a .kml file. I have already set my filepath name and document name. I’m following the directions to the letter and it always says “not found” for the latitude and longitude. Why is this?
May 30th, 2012
Kango
@ Mitch: I have the same problem as you. In case you have found by the meantime a solution, it would be great to share it with me (the_kango@hotmail.com)
If someone else would know what to do, please feel free to help us 🙂
Thanks,
Kango
Jun 12th, 2012
Mitch
Kango,
Yeah. I believe the link that’s going out from the excel spreadsheet is going to yahoo or bings latitude/longititude creation area that has been closed. I don’t think this excel spreadsheet will work unless theres a patch done or something with it is fixed. Which is a shame because we were so close to creating that .KML file which would have been the last step before plotting it on Google Earth without having to upload my business data onto the web (which I don’t feel comfortable doing). Now I will have to buy mapping software -$$$. I have scoured the internet for a program like this for hours and hours without any real luck. Please anyone let me know if you find anything that fixes this issue or if you can find a different method for doing this.
Mitch
Jun 19th, 2012
bruce
Hello
There are various capabilities here that might help
http://ramblings.mcpher.com/Home/excelquirks/getmaps
Someone previously asked about changing colors/pins etc.
That’s is described here
http://ramblings.mcpher.com/Home/excelquirks/getmaps/mapping/circles
Finally here is a quick start on the various topics that may help to get going quickly.
http://ramblings.mcpher.com/Home/excelquirks/snippets/steps
Jun 19th, 2012
Leave a Comment