In Part 1 of this series I demonstrated how you can use the Bing Maps feature in Excel 2013, 2016, or on an iPad to create interactive maps with just a few mouse clicks. This article will continue the series by demonstrating how you can go beyond placing points on a map to identify addresses. You will be able to use the map feature to illustrate numerical values on a map, such as showing sales amounts by customer, city, or state. If you’re not familiar with this mapping feature in Excel, be sure to read Part 1 first.
In Part 1, we created simple maps that displayed points on a map for each address. We’ll now extend the technique further by mapping data by city/state to show how the map points can be sized relative to a set of numbers associated with the points being mapped.
If you’re comfortable using an ODBC connection to QuickBooks, you can easily create a query that will create the necessary lists in the proper format. Otherwise, you have a bit of legwork ahead of you. If you’re not fluent in the nuances of working with QuickBooks data in Excel, or using pivot tables, read about these techniques before you continue on:
- Exporting sales by city or state from QuickBooks
- Merging two QuickBooks reports together
- Using pivot tables to analyze QuickBooks data
You may need one or more of the approaches I discuss in those articles to get your QuickBooks data into the necessary format. Assuming you have the necessary background, let’s use the Sales by Customer Detail report in QuickBooks to map sales data. You can use other reports in QuickBooks to map purchases by vendor, salaries by employee, and so on. We only need three fields to create our map, as illustrated in Figure 1:
- Name City
- Name State
Then, as illustrated in Figure 3:
- Delete column A.
- Delete row 2.
- Delete the total row from the report.
The final data preparation step is to create columns that combine certain report fields together.
- In cell D1 type the words City/State.
- In cell D2 type this formula =A2&”, “&B2, and press Enter.
- Click on cell D2 again and double-click the Fill Handle.
At this point your spreadsheet should look like Figure 4.
To summarize the data, as illustrated in Figure 5:
- Click any cell within the list.
- Click on Excel’s Insert.
- Select Recommended PivotTables.
- Double-click the second pivot table on the list, which should show City/State and Amount. Note that this feature may not work as described if Amount isn’t the last column of your source data.
- Click the Design tab (which only appears when your cursor is within a pivot table).
- Select Grand Totals.
- Select Off for Columns and Rows.
You’re now ready to map the data. To do so, follow the steps in Figure 6:
- Click any cell in the pivot table and press Ctrl-A to select all of the cells. (Note: If you forget to remove the total line, you’ll end up with a data point in Germany, even if you don’t actually have any customers there.)
- Click on Excel’s Insert.
- Click on the Bing Maps app in Excel 2013 or later.
- Click the Pin.
The circles that appear on the map represent your sales by city and state. Inconsistencies in your data will result in odd map points, such as how East Bayshore, CA gets misinterpreted as E Bay Shore Rd, Owen Sound, ON N4K, Canada, as shown in Figure 7. One way to fix these is to clean up your source data:
- Click any cell within the report that you exported from QuickBooks.
- Press Ctrl-Shift-L to turn on Excel’s Filter feature (or click the Filter command on Excel’s Data menu).
- Click the arrow in cell D1 to display the Filter dropdown list.
- Type a portion of the problematic city name in the Search box, such as East, until that city name is the only choice on the list.
- Press Enter to display only those rows.
- Select the cells in column D, and then in this case type Bayshore, CA and then press Ctrl-Enter. Doing so corrects all of the misspellings at once.
Repeat these steps to correct city/state combinations as needed, and then return to the pivot table that you created. Right-click anywhere on the table and then select Refresh so that the pivot table reflects your changes. Your map will then update automatically.
You now have the foundation for looking at your data several ways, as shown in Figure 8:
- Uncheck the Amount field checkbox in the PivotTable Field List to show data points by city instead of circles the represent amounts.
- Uncheck the City/State checkbox, and click the State checkbox instead. To update the map, click any cell within the pivot table, press Ctrl-A, and then click the map pin command at the top of the map. Your sales should now be represented by state.
- Uncheck the Amount field checkbox to create a map that shows an icon for each state in which you do business.
- Click the Gear icon at the top of the map to change the pin color.
- Click the Filter icon at the top of the map to specify which points you wish to see. Be sure to click the Apply command at the bottom of this filter list; otherwise, your map won’t reflect your filtered choices.