From Excel to Google Maps

An address map is a valuable way of representing points of data. We’ve been asked to create membership maps for organizations. A request by a church to make a map showing church member addresses inspired us to write up this method of making an address map.

Google Map’s Your Places 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.

(Note that some of the menus and names of interface features change when Google decides to update this service.)

Custom Google Map of church members
Custom Google Map of church members

GOOGLE ACCOUNT & MICROSOFT EXCEL

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.

GOOGLE’S YOUR PLACES

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.

The hamburger menu
The hamburger menu

This opens the Google Maps menu with different options. You’re interested in Your Places.

Google Maps menu of options
Google Maps menu of options

Now click Your Places mid-way down the menu.

Google Maps and Your Places
Google Maps and Your Places

CREATE YOUR MAP

Next, click CREATE MAP at the bottom of the menu. This brings up the new map box in 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.

The untitled map and untitled layer box
The untitled map and untitled layer box
Dialog box to edit map title
Dialog box to edit map title

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.

Choose file to import dialog box
Choose file to import dialog box

Drag your Excel file, or other file type, to the import box.

CONNECTING EXCEL COLUMNS TO GOOGLE PLACEMARKS

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.

Dialog box for choosing Excel columns for placing markers on map
Dialog box for choosing Excel columns for placing markers on map

Now, in the Choose a column to title your markersbox, 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.

Choose column for marker title
Choose column for marker title

EDITING ICONS

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

The edit colors and shape dialog box
The edit colors and shape dialog box

A COUPLE PROBLEMS

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

Marker call-out information box
Marker call-out information box

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.

For more information on Google Map’s Help page or try Reddit.