- Excel Book Excerpt
Excel Use MapPoint to Plot Data on a Map
This page is an advertiser-supported excerpt of the book, Learn Excel 2007-2010 from MrExcel - 512 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.
Problem: I have a retail store and keep a mailing list of everyone who shops in my store. The data contains street address, city, state, and zip code. How can I view my customers on a map so I can easily see where they’re concentrated.
Strategy: Microsoft MapPoint is an extra program that you can buy from Microsoft, in either a North American version or a European version. With a US$299 list price, it is fairly expensive. However, Microsoft often gives it away for attending its seminars. You also might be able to pick up a copy on eBay for far less than the list price. MapPoint allows you to do some very cool geographic analysis of data.
I prefer to create the map from the MapPoint instead of using the Excel add-in. Save your data in Excel. Ideally, you will have headings in row 1 and data starting in row 2. If you have zip codes, they need to have leading zeroes in the cell instead of using a “00000” format code to make it look like there are zeroes in the cell. Save the file as either .xls or .xlsx file types. As of MapPoint 2010, the program is still not reading the .xlsm or .xlsx formats. Close the Excel file before opening in MapPoint. In MapPoint, choose Data, Import Data Wizard and browse to your file.
MapPoint will match your addresses to its database. Soon, you will have a map that shows your customers. Your store had one customer from Alaska and several from Florida, so the map initially zooms out to show those customers.
Figure 1343 The initial map is zoomed out to show all customers.
To get a better view of the majority of your clients, drag your store to the center of the screen. Use the Zoom In icon to zoom to your region.
Figure 1344 Zoom in for a regional view.
If your original data included street addresses, you can zoom in further. You can now see from which sections of your city your customers generally come. This information can help you to target newspaper or billboard advertising.
For more resources for Microsoft Excel: