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:
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.
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!
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!