Quickly mapping a spreadsheet with addresses

There are many ways that you can quickly take a spreadsheet of data with street addresses and turn that into an interactive map. Sadly several of them require you to pay for a special license (e.g. Esri Maps for Office) or require knowledge most people don't have. Here are a few quick ways to do it for free without a lot of work.

If you are in a hurry, have separate fields for street address, city, and state, and want the simplest solution, go to http://batchgeo.com/ and simply paste in your data, and hit map now (or validate and set options to get more control over how it looks and which fields to use for the map). You will get a link to share with others to view the map, and you will get a special edit link too. The map will look something like this (although you can use different colors based on values in a field):


What if you want to be able to make updates to a spreadsheet online (perhaps via a form) and have the map update automatically? Start by making sure that you have a single field in your spreadsheet with the complete address (I recommend calling this field Address or FullAddress). If you have separate columns for street address (e.g. "123 Main St"), city, state, zip, and country, that's fine but you will need a new column that concatenates (combines) them. Fortunately this is pretty easy to do in Excel or Google spreadsheets or other similar software. Let's assume you have street address in column A, city in column B, state in C, zip in D, and country in E. In column F (e.g. cell F2 if you have the column names in row 1), you would type in the following:
=A2&", "&B2&", "&C2&", "&D2&", "&E2
This formula will combine each of the five fields, and separate each one by a comma and a space to match standard address formats. You can then copy this cell, select the range of cells where you need this new field (e.g. if you have 50 rows, select them all to row 50) and paste it in, which will give you the new combined address for all of your data.


Once that's done, the rest is pretty easy. There are two ways to proceed depending on your preference

If you have a single "title" field and a single "description" field you want to share, your data can be shared publicly, and plan to host the map on your own web page (i.e. you can host an html file or copy / paste html into a template),
the simplest way is probably to upload your spreadsheet to Google Drive, use this batch geocode page to generate longitude and latitude for your data (x and y coordinates), and then use this spreadsheet mapper page to generate a very simple map. It might look something like this (in this case I don't have a real title or description, so I just show phone and address):


If you want more control over how the map looks, and don't have a place to host your map (you just want a link you can share with others), or if you have sensitive data, you could use Google Fusion Tables (which I use a fair amount for this kind of work). In this case, you can skip the geocoding step (Fusion Tables can map addresses as long as they are in a single field). From Google Drive, hit the red "Create" button, and select Fusion Tables. Choose "From Google Spreadsheets" at left, and select the one you want to use. Hit next, make any changes you like, then hit finish. If it doesn't correctly pick your address field as the location (it will be highlighted in yellow), go to the Edit Menu, Change Columns, pick your full address field, and check the location box.

Once that's done, click the Map tab and you will see your basic map. If you don't see any markets, you may need to manually go to File and then Geocode for it to properly map all of your data (after which the highlighting goes away). You can further customize it by using the Tools menu and Change Map Styles (for changing the markers) or Change Info Window Layout (for the text in the popup). You can use the Publish option under tools to get a link to share, or html to copy and paste to put the map in your site. It looks pretty similar by default (this is without making any changes):


There is of course much more you can do, but either of the above options should get you a map in under an hour (once you have the process down, it only takes 5-10 minutes). BatchGeo is the fastest and simplest, Fusion tables offers the most flexibility, and if you already have your data in a Google spreadsheet and plan to make changes, it might be easier to have a map directly based on the spreadsheet. Happy mapping!