Hack 13. Add Maps to Excel Spreadsheets with MapPoint

If you have both Microsoft Excel and Microsoft MapPoint, you can add maps to your spreadsheets with a few clicks.

A lot of people manage address lists and other address-based data in Microsoft Excel, but while Excel provides all kinds of graphing capabilities, it doesn't know much about maps. If you add Microsoft MapPoint to your system (another $250, admittedly), you can show your Excel data on a map quite easily.

As the November 2004 election is coming up as I'm writing this, I've been doing a lot of work with voter registration rolls. One of my spreadsheets, actually an export from Access, is a list of street addresses and party affiliations, as shown in Figure 1-28.

Figure 1-28. An address-laden Excel spreadsheet

Because MapPoint 2004 is installed on the same computer, Excel shows an extra toolbar button to the left of the font-choice drop-down box. Clicking on that button inserts a MapPoint object on the spreadsheet and also starts up the Link Data Wizard, shown in Figure 1-29. (If you have more than one area of data on the spreadsheet, select the part you want to map before clicking the button.)

Figure 1-29. The Link Data Wizard's initial interpretation of the address data

MapPoint will make an educated guess about data types based on headers in the document, and you can use the drop-down boxes to correct it if it's wrong. MapPoint will accept a variety of geographic data types, varying by country. For the United States, it accepts any three of address, city, county, state, country/region, ZIP Code, three-digit ZIP Code, census tract, metropolitan area, latitude, and longitude. Once you've chosen the correct data types, MapPoint will ask to identify a primary key, as shown in Figure 1-30.

Figure 1-30. Choosing a primary key in the Link Data Wizard

If all you plan to plot is the one area currently being examined, you don't really need a primary key. If, however, you're combining multiple sets of data, such as customer addresses and customer spending amounts, you'll want something like a customer ID number to act as your primary key connecting the data.

When you click Finish, you may not be quite done, however. MapPoint will do what it can to interpret your data, but it may not know everything, especially if roads have been built or names changed over time. If that happens, you'll see something like Figure 1-31, asking for help.

Figure 1-31. Helping MapPoint find an address

In this case, I had to skip a few records, but there were a few times where the problem related to abbreviations that didn't match up, and it was easy to pick a choice that matched. Once you finish this process, you'll be rewarded with a pushpin map like that shown in Figure 1-32.

Figure 1-32. The initial map generated by MapPoint

The pushpins are sort of useful, but they don't reflect all the data that was included, notably party affiliation. Fortunately, MapPoint lets you do more than just add pushpins. The toolbar now shows some additional icons. Just above the zoom adjuster is a button for "Data Mapping Wizard," showing a set of green fields. Clicking on that brings up many more options, shown in Figure 1-33.

Figure 1-33. MapPoint presentation styles

There are lots of different choices here. Shaded areas are great if you're aggregating one kind of data, while pie and column charts can show details for a given area. The pushpin map already in use is an option, but for showing party affiliation, the Multiple Symbol choice is likely the best option. If you click the Multiple Symbol button and choose Next, you'll be asked where to get the data that chooses the symbol, as shown in Figure 1-34.

Figure 1-34. Choosing the source for the data behind the symbol

MapPoint offers lots of choices for getting data. MapPoint itself includes demographic data for the United States down to the Census Tract level, and it will let you import or link data from other sources, such as spreadsheets or databases. In this case, the data needed, party affiliation, is already in the data associated with the map, so just clicking the Next button works fine. The Data Mapping Wizard then asks what data to use and what level to show them at, as shown in Figure 1-35.

Figure 1-35. Choosing the particular data behind the symbol

Figure 1-36. An error, as MapPoint has a limited number of multiple symbols supported at once

In this case, affiliation is the key, and street address is an appropriate level for displaying it. MapPoint can only display up to eight different choices here, and voters in this area have nine different political affiliations, so MapPoint displays the warning shown in Figure 1-36.

Unfortunately MapPoint doesn't let me choose to keep the two Working Families voters in the list, but 2 out of 841 voters and 1 of 542 addresses is probably okay for this demonstration. The next screen, shown in Figure 1-37, lets you choose the symbols to use on the map to represent different affiliations.

Figure 1-37. Choosing symbols for the data

The drop-down boxes let you choose symbols and colors, and you can also change the labels used. In this case, because many of these houses are right next to each other, I chose the smallest symbol available and used color to distinguish party.

When I'm done, I have a completed map, shown in Figure 1-38.

Figure 1-38. A completed map

Figure 1-39. A completed map, zoomed in for higher detail

The house-by-house presentation looks better when you zoom in further, as shown in Figure 1-39.

Figure 1-40. Opening the spreadsheet in a copy of Excel on a computer without MapPoint installed

As long as you open the spreadsheet on a (Windows) computer running MapPoint, you'll be able to zoom around the map, change the icons, link new data, and more. You can also open the map in the full MapPoint program if you like and save a copy out for other work. However, if you open the spreadsheet in a copy of Excel without access to MapPoint, you'll still have a picture, as shown in Figure 1-40, but with no interactivity.

Simon St.Laurent