Google’s My Maps is an easy and free portal to seeing your spreadsheet data in an online map. While there are more involved ways of accomplishing this, including linking an online version of your spreadsheet data “live” to Google Maps, let’s explore the simplest way of mapping your Excel data.
To start, you’ll need a Google account. You can create one at Google. For a user name either create a Gmail address or just use an existing, non-Google email address.
The other pre-requisite you’ll need is a Microsoft Excel spreadsheet with location data. For this blog post, I employed a spreadsheet of parishioners provided by a church office that has columns for name, street address, city, state, and zipcode.
Once your account is set up and your spreadsheet populated with data, you’re ready to begin. Go to Google Maps and click the three-line “hamburger menu” icon in the upper-left.
This opens the Google Maps menu with different options. You’re interested in My Maps.
Click My Maps from the menu and then click CREATE MAP at the bottom of the menu. This brings up the new map box which you’ll edit information about the map and import data into one of its layers.
In the new map box, click on Untitled map and give the map a suitable name in the pop-up Edit map title and description box.
Next, click on “Import” in the new map box and then in the pop-up import box, drag and drop the icon of your spreadsheet. This initiates Google’s processing of your data.
When the import completes, Google opens the Choose columns to position your placemarks box. Checkmark the boxes (e.g., name, address, city, state, zip) that are relevant for your data and then click Continue.
Now, in the Choose a column to title your markers box, checkmark the appropriate box (for example, Lastname). The column you choose provides the title to the call-out information box that pops up when you click a marker on the map. Click Finish and Google completes importing your data.
The resulting map should look familiar: Google’s iconic balloon icons saturate the map, each icon representing a row of your spreadsheet. If you want to change the icon, Google gives you some options. To access these, hover your mouse cursor over All items and click the paint bucket icon at the right end of the field. For my map, I changed the default balloon icon for a red dot.
That was easy. Easy, that is, unless your spreadsheet has location errors or it just plain confuses Google’s addressing software. You may see a warning in the new map box about rows with errors that Google is unable to display on the map. To correct, click Open data table and either edit the offending data or delete the rows (if you can live without their data). Also, zoom out on the map and see if there are odd markers located where they shouldn’t be. For these, right-click on each marker and choose Delete.
Another data problem you may confront is that not all of your data was imported. As of this writing, Google Maps imports a maximum of 2000 addresses. This may require you to break your spreadsheet into two files and import both.
You can add more markers to your map by creating a new layer and entering an address (or, sometimes, the name of a point-of-interest) in the Search box to the right of the new map box. You can add your Google Map to your website by creating an iframe on the web page that links to the URL of your new Google Map.