10. Presenting Excel Data on a Map Using Microsoft MapPoint
Plotting Data Geographically
In many cases, the best way to present data is to plot it on a geographic map. Mapping software used to cost thousands of dollars. However, Microsoft offers a product called Microsoft MapPoint 2010. MapPoint is available in editions for North America and Europe.
Figure 10.1 shows a Starbucks database plotted on a map. Note how the stores are concentrated in the heart of the city, but appear in any semi-populated region. Any dataset with a geographic component such as street addresses, zip codes, postal codes, latitude and longitude, states, or provinces are suitable for mapping.
With MapPoint, you can zoom in to get a view of locations by street in a neighborhood (see Figure 10.2) or zoom out to see trends over the whole country (see Figure 10.3).
When you install MapPoint, new icons appear on the Add-Ins tab that allows you to plot your data on a map within Excel. For a bit more functionality, you can save your Excel data as an Excel workbook and import it into MapPoint.
TIP
MapPoint 2010 has a list price of $299 and regularly sells at Amazon.com for $269. However, Microsoft seems to regularly give MapPoint to CIOs as an incentive for attending conferences. Consequently, you can often find brand-new, factory-sealed versions of MapPoint for sale on eBay for less than $100.
Figure 10.1 Stores plotted on a map.
Figure 10.2 You can zoom in to a neighborhood view.
Building a Map in Excel
Figure 10.4 shows a database of locations for a chain of stores in the United States. You hope to sell products to those stores and need to analyze their geographic locations.
To create a map for this data in Excel 2010, you follow these steps:
1. Make sure that MapPoint is installed on your computer. You should have an extra Add-Ins tab available on the ribbon, with one or more MapPoint Map icons.
Figure 10.3 You can also zoom out to a country view.
Figure 10.4 This data lends itself to geographic analysis.
2. Select your data in Excel, including headings. MapPoint recognizes headings such as Latitude, Longitude, City, State, and Zip Code.
3. Click the MapPoint Map icon on the Add-Ins tab.
TIP
At this point, you might encounter an annoying bug. MapPoint 2010 can finally import data from XLSX and XSLM files. However, the add-in for MapPoint 2010 will insist that your file has not been saved and offer to save it for you. The resulting Save As dialog box indicates a file extension such as XLSX, but the file is actually saved as an XLS file. Therefore, as you go along with saving the file, you might be presented with a list of file compatibility issues. If you need your file to stay as an Excel 2007-2010 file type, consider saving the relevant data to an Excel 2003 file.
4. Excel displays the Link Data Wizard dialog. For each column in your selection, Excel shows the column heading and then a drop-down that identifies the data type for that column (see Figure 10.5). Valid data types are Name, Address, City, County, State, Country, Zip Code, Census Tract, Latitude, and Longitude. If you have columns in your data that are not in the list, select <Other Data> for that column. For example, if you had sales figures for each store, you would select <Other Data>.
Figure 10.5 For each column in your data set, either identify it as a geographic column or select <Other Data>.
5. Click Next, and then click Finish. Excel attempts to find a matching location for each record in your database.
6. After Excel finds all the exact matches, it reports any items that were not matched. For example, in Figure 10.6 the spelling of Assaria Kansas appears to be wrong in the dataset. For each unmatched record, choose a location and click OK or simply click Skip Record if you do not know the location.
Figure 10.6 You usually have to manually match a few records from a dataset.
7. Repeat step 6 for each unmatched record. After you match the last record, Excel plots each record as a thumbtack on a map and zooms in to show the complete set of data points.
8. While the map is active, the Excel ribbon is replaced with a MapPoint menu and toolbar. From the toolbar, select the Legend and Overview icon, as shown in Figure 10.7. Excel adds a sidebar showing the Pushpins set.
Figure 10.7 Toggle on the legend so you can change the properties for the pushpin set.
9. Right-click the pushpin in the legend and select Properties
10. In the Properties dialog, change the symbol from a thumbtack to a small circle, as shown in Figure 10.8.
11. Click OK to close the Properties dialog box.
12. Click the Legend and Overview icon in the toolbar to remove the legend.
NOTE
Excel always tightly crops the map to include all the pushpins in the dataset. Use the Zoom Out icon in the toolbar to see a 50,000-foot view of the data. In Figure 10.9, you can see that the stores are concentrated in the southeastern part of the United States.
Navigation in MapPoint 2010 has changed since previous versions. The Select icon is gone from the toolbar. If you click the map and drag, you can move the map to re-center the portion of the map in the visible window.
Two magnifying glass icons surround a zoom slider in the toolbar. Avoid the temptation to use those magnifying glasses to zoom in and zoom out. You have much more control by nudging the zoom slider in the MapPoint toolbar.
Figure 10.8 The small circle icons work better than thumbtacks on a map with hundreds of mapped points.
Figure 10.9 You can zoom out to see the entire country.
While the map is active, you can use the menu commands View, Zoom Map, To Data to zoom in to all of the data points. If you inadvertently zoom to far out using the Zoom Out magnifying icon, you can use the Zoom To Data to get back to a view that covers all of your data points.
A drop-down appears at the bottom right of the MapPoint toolbar. If your map is too cluttered, open this and change from a Road Map to a Data Map.
Showing Numeric Data on a Map
If your dataset includes some numeric data, you can represent that data on a map. MapPoint offers many options for representing numeric data:
• Shaded Area, Shaded Circle are like the Excel 2010 icon sets. You can use from two to eight colors to represent different levels of sales or units or population.
• Sized Circle is somewhat like the data bar concept. Each point on the map will be marked with a circle. Larger circles represent larger sales.
• Multiple Symbol is similar to a Shaded Circle, except you can use different shaped symbols for different levels of sales. If you would prefer a triangle for large sales and a square for small sales, the Multiple Symbol
• Pie Chart and Column Chart are useful for showing component sales for each point. You might want to compare hardware and software sales by state.
• Sized Pie Chart combines the Sized Circle concept with the Pie Chart concept. The size of the circle indicates overall sales. The components will be shown as wedges within the pie chart.
• Series Column Chart is great for showing a small time series chart for each point. If you want to show sales by year for the last 3 to 5 years for each state, this chart will work.
In Figure 10.10, you have sales by state over four consecutive years. This data is ideal for using a Series Column Chart in MapPoint.
Figure 10.10 Create a time-series chart for each state.
To create a time-series chart, follow these steps:
1. Select your data including the headings.
2. From the Add-Ins tab, select MapPoint Map.
NOTE
Pay close attention to the Link Data Wizard dialog box. This box is overly anxious to find some column that looks like zip code information. In Figure 10.11, the dialog initially guesses that the 2007 sales column is zip code. After testing with various datasets, the erroneous Zip Code field can happen in any column that happens to start with five integer values less than 99,999. You will notice that because one field is identified as a zip code, the information bar at the bottom of the dialog says that it is going to match the data fields to zip codes instead of states.
Figure 10.11 Excel tries to find a column that might contain zip codes.
3. Open the drop-down for the column that is misidentified as a zip code. Change the data type to <Other Data>. This should cause the information bar to indicate that records will be matched to State (see Figure 10.12).
4. Click Next.
5. In the next step, MapPoint asks for you to identify a column that will be the primary key. In this example, use State. Click Finish. The map appears with a single pushpin in each state.
6. In the MapPoint toolbar, click the icon for Data Mapping Wizard.
7. In the Data Mapping Wizard – Map Type dialog, select Series Column Chart, as shown in Figure 10.13. Click Next.
8. In the Data Mapping Wizard – Dataset dialog, leave the default setting of Edit an Existing Data Map. Click Next.
9. Choose each of your yearly sales fields in the Data Mapping Wizard – Data Fields dialog. Choose to show the data by State (see Figure 10.14).
10. In the Data Mapping Wizard – Legend dialog, choose if your data should be plotted as a Continuous Range or a Logarithmic Range. Click Next and then click Finish.
Figure 10.12 After correcting field type drop-downs, the information bar corrects itself automatically.
Figure 10.13 Select Data Mapping Wizard.
TIP
The logarithmic option mentioned in step10 is good if you have series with dramatically different orders of magnitude.
11. Change the Map Style drop-down from Road Map to Data Map. This clears the road data from the map, making it easier to see the column charts.
12. Nudge the MapInfo zoom slider at the top of your screen slightly to the left. This will zoom out a bit.
Figure 10.14 Choose the numeric columns for the column chart.
13. Click outside of the map to embed the map in the worksheet. To later edit the map, double-click the map.
Figure 10.15 shows the completed map. Note that the legend identifies the maximum value of the vertical axis and also identifies that the years run from 2007 to 2010.
Figure 10.15 The completed map shows column charts for eight states.
Using Other Map Styles to Illustrate Data
The shaded area map style is great if you have data that can be aggregated by zip code, census tract, county, or state. For example, Figure 10.16 shows an earthquake index that is plotted by county.
You can use the dozens of datasets that ship with MapPoint. To do this, after inserting a map, select Data, Data Mapping Wizard, and then select to use Demographic Data. Using this data, you can shade your map by income, percentage of owner-occupied housing, even earthquake index as shown in Figure 10.16.
Figure 10.16 This shaded area map shows an earthquake index by zip code.
CASE STUDY: MAPPING YOUR CUSTOMERS
Almost every business has a mailing list of its customers. If you have this data in Excel and you have MapPoint, you can plot up to 10,000 customers on a map.
Seeing your customers on a map helps you to plan advertising and helps you understand how far your customers travel to reach your location. To plot your company’s customers on a map, follow these steps:
- Import your customer list into Excel. Be sure to add headings such as Name, Address, City, State, and Zip Code. Save the data as an Excel 2003 workbook.
- Start MapPoint. Select Data, Import Data Wizard.
- Browse to and select your Excel file.
- Confirm the field mapping. Click Next and then click Finish. MapPoint matches your fields up with known addresses.
- If there are unmatched records, MapPoint shows you any close guesses. When you are matching at the address level, you are likely to have a high rate of unmatched addresses. If you are in a hurry, click Skip All Records to map the records that do match.
- Select a pushpin map. Click Next.
- Change the symbol from a thumbtack to a small circle. Click Finish.
Typically, you might have a few stray customers who visited your business while they were traveling. These distant customers cause MapPoint to zoom out to show all the customers.
Click the map and drag to put your business in the center of the map. Use the zoom slider to zoom in to the area around your business. You now have a map of the densest concentration of customers, as shown in Figure 10.17. This enables you to determine which newspapers or radio stations might be effective for reaching other customers who live in the same area as your current customers.
Figure 10.17 This is a powerful type of map for a business owner. It helps you see where your customers are from and think about ways to reach other customers in that area.
To see a demo of mapping customers, search for “MrExcel Charts 10” at YouTube.
Next Steps
In Chapter 11, “Using SmartArt Graphics and Shapes,” you will learn how to use Excel’s new business diagramming tools to communicate relationships and organization charts.