When you're working with geographical data, sooner or later you'll probably want to plot it on a map. Prior to Excel 2002, Excel contained a tool to create maps from its data. But with newer versions, you need special software such as Microsoft MapPoint 2004 ($299; varies by country) to create map-based charts. MapPoint can work both within Excel and on its own; here we will explore MapPoint's integration with Excel.
How you create and prepare data largely determines how easy it is to map that data. You need a list with enough information for MapPoint to identify locations. To plot by state, you need a column in your data table for the two-character state name abbreviations. To plot by county, you need the county and state, in separate columns. To plot addresses, you need columns for address, city, ZIP code, state, and so on.
Mapping Inside Excel
Use the sample data in Figure 1 to create a simple MapPoint map in Excel. Type this data into a range of cells and save your worksheet; MapPoint can't use the data until the worksheet is saved.
Select the data, including the headings, and click on the MapPoint button on the Excel toolbar. A MapPoint map and the Link Data Wizard dialog, where you tell MapPoint about your data, appear.
The first column should already be identified as Data Type: State; if it's not, select it now. The second column will probably be wrongly identified as a 3-digit ZIP code; if it is, select Other Data from the list. If you don't do this, MapPoint will plot the third column (Sales 2003) using column two as the location ZIP code and you'll end up with data locations in Alabama, Iowa, Virginia, and Ohionot what you want. Ensure that the Sales 2003 column has a data type of Other Data and click on Next.
MapPoint will ask you to identify a primary key; for our data, this is the State column, so select this and click on Finish. You now have a map of the U.S. with pushpins in the states of WA, CA, NV, and ORnot a particularly useful result. To show anything more meaningful, you must customize the map.
To edit the chartwe'll convert the pushpins to small chartsclick on the Data Mapping Wizard button on the toolbar and choose Column Chart. Click on Next and on Next again. Check the boxes for both Sales 2002 and Sales 2003, and ensure that State is selected in the "Show the data by" area. Click on Next.
You can now configure the data ranges. They'll look better if they're rounded numbers, so set the start value to 0 and the end value to 800; this range encompasses all our data and divides neatly into four parts. On the right of the dialog, type the Range labels to match the new range values. For the Legend title, type "Sales 2002 & 2003 by State" and click on Next, then on Finish. You now have small column charts on your map.
To remove the map clutter, choose View | Map Style | Data Map. To update the map when you alter the worksheet data, select the map and choose Data | Update Linked Records.
You can also go the other way and create a map in MapPoint by importing Excel data. In this case, your data must begin in the first row of your worksheet with column headings (not the title). In MapPoint, choose Data | Import Data Wizard, select your worksheet, and then identify the sheet with the data. If you intend to link the map to the data (Data | Link Data Wizard), you will need a column containing data unique to each record to configure as your primary key (like the State column in our earlier example). MapPoint uses this unique value to identify records when updating them.
There are many more mapping options to experiment with. Shaded maps display different-colored areas according to your data values, and there are also shaded and sized pie and circle charts. MapPoint includes demographic data, which you can combine with your own data; for example, to plot sales against populations, age groups, or income levels.
Interested but not convinced? To evaluate MapPoint, order a 60-day trial ver-sion ($7.95 for North America; $12.95 for North America and Europe) from www.microsoft.com/mappoint/2004/trial.mspx .
Helen Bradley is a contributing editor of PC Magazine. She specializes in writing hands-on tutorials. Her columns appear regularly in a number of publications in Australia, Canada, the U.K., and the United States.