Software

Put your Excel data on the map

Does your company do business in more than one state or country? If so, here's an Excel tip that makes it easy to display important data organized by location.

Every self-respecting Excel user knows how to create graphs and charts. You enter some data, click the Chart Wizard icon, and you can choose from a number of different chart styles and options.

In a corresponding TechMails tip, we told you how you can literally put your values on a map. If your company does business in more than one state (or country), you can use Excel and Microsoft Map together to present your data in a compelling, picturesque style.
By default, Microsoft Map doesn’t get installed when you run a typical installation of Office 97. When you run the Office setup program, you won’t see a check box for Microsoft Map either. You must select all of the “optional” components and finish updating the Office installation. Then Microsoft Map will be available.
But if you're using Office 2000, you're in luck. While the Map icon may not appear on your Standard toolbar, you can easily add it. Go to Tools | Customize and select the Commands tab. Choose Insert from the Categories list box, then drag Map from the Commands list to the Standard toolbar.
Select the data and click the Map icon
Here’s how to map data associated with geographic regions in the United States.

Start by entering the two-letter abbreviation of the state in one column and enter the value associated with that state in the column beside it. (You must enter the abbreviation in uppercase letters. For a complete list of state abbreviations, click here.) Then select the raw data, including the column headings, as shown in Figure A.

Figure A
To create a sample map, start by selecting the state abbreviations and their associated values.


After selecting your data, click the Standard toolbar’s Map button once. Then click in your worksheet and “drag” to draw the outline for your map. When you release the mouse, Microsoft Map will display a message like the one shown in Figure B. In this case, you can select the United States only (with an Alaska and Hawaii inset), or you can select United States In North America, which will display the entire continent.

Figure B
Microsoft Map recognizes the state abbreviations in our sample data and offers a choice between two U.S. maps.


Figure C shows what our map looks like after we selected the United States (AK & HI Inset) option.

Figure C
Here’s what our map looks like after the program color-coded each state with our data. (No data was available for the states in green.)


Customize your map
You don’t have to settle for the default settings for your map. You can change the color scheme of the map and the data legend and even plot the location of major highways and cities for almost any country.

For instance, to plot the location of all the major cities in the United States, double-click the map to open it, then right-click the map, and choose Add Feature. Select US Major Cities (AK & HI Inset) and click OK. Figure D shows our sample U.S. map after we added the major cities and highways features.

Figure D
We embellished this U.S. map by plotting the locations of major cities and highways.


In addition to the features you can add by right-clicking a map, you can use the Microsoft Map Control dialog box (shown in Figure E) to customize your map even more. (We won’t go into the details here, but using this dialog box effectively takes a bit of practice.)

Figure E
You can further customize your maps by using the Microsoft Map Control dialog box.


If a picture is worth a thousand words, you can use Excel and Microsoft Map together to create an impressive graphic for use in your printed handouts or slide shows.
Get valuable tips on creating tables, formatting pages, links to Word resources, and much more, all delivered straight to your inbox. Best of all, it's absolutely free. Sign up for the Excel TechMail today!
1 comments
martin.schmidt7111
martin.schmidt7111

I've been searching for maps solutions like this for quite a while and while this one is alright, I finally found some proper solutions on www.maps4office.com - Unfortunately these arent free but fairly cheap compared to other stuff I found.

Editor's Picks