Tech Trends

Interactively Map Your QuickBooks Data in Excel – Part 2

Written by David Ringstrom

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:

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
  • Amount
Interactively Map Your QuickBooks Data in Excel

Figure 1: Customize the Sales by Customer Detail report using these fields.

 

Sales by Customer Detail Report

Figure 2: I’ll use the Sales by Customer Detail report as the basis for the sales maps that I’ll create.

Then, as illustrated in Figure 3:

  1. Delete column A.
  2. Delete row 2.
  3. Delete the total row from the report.
Sales by Customer Detail Report

Figure 3: You’ll typically want to remove these elements from QuickBooks reports that you export to Excel.

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.

Summarize data with pivot table

Figure 4: I’ll summarize this data with a pivot table in preparation for display with the Bing Maps app.

To summarize the data, as illustrated in Figure 5:

  1. Click any cell within the list.
  2. Click on Excel’s Insert.
  3. Select Recommended PivotTables.
  4. 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.
  5. Click the Design tab (which only appears when your cursor is within a pivot table).
  6. Select Grand Totals.
  7. Select Off for Columns and Rows.
Summarize the data prior to mapping it

Figure 5: Summarize the data prior to mapping it.

You’re now ready to map the data. To do so, follow the steps in Figure 6:

  1. 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.)
  2. Click on Excel’s Insert.
  3. Click on the Bing Maps app in Excel 2013 or later.
  4. Click the Pin.
Pivot table data converted to an interactive map

Figure 6: Pivot table data can be converted to an interactive map with just a couple of mouse clicks.

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:

  1. Click any cell within the report that you exported from QuickBooks.
  2. Press Ctrl-Shift-L to turn on Excel’s Filter feature (or click the Filter command on Excel’s Data menu).
  3. Click the arrow in cell D1 to display the Filter dropdown list.
  4. 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.
  5. Press Enter to display only those rows.
  6. 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.

 

Misspelled city/state combinations

Figure 7: Misspelled city/state combinations can result in oddly placed map points.

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:

  1. Uncheck the Amount field checkbox in the PivotTable Field List to show data points by city instead of circles the represent amounts.
  2. 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.
  3. Uncheck the Amount field checkbox to create a map that shows an icon for each state in which you do business.
  4. Click the Gear icon at the top of the map to change the pin color.
  5. 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.
View data

Figure 8: You can now use an assortment of features to view your data in a variety of ways.

About the author

David Ringstrom

David Ringstrom, CPA, is the president of Accounting Advisors, Inc., an Atlanta-based spreadsheet and database consulting firm he started in 1991. Throughout his career, David has spoken at conferences on Excel, and he currently leads dozens of webinars each year on Excel, QuickBooks, and other software. He has served as the technical editor for over 25 books, including several editions in Wiley’s QuickBooks for Dummies and Quicken for Dummies series. In addition to writing for QuickBooks and Beyond, David is the Tech Editor at Large for AccountingWEB and Going Concern. He also offers live webcasts and self-study courses through CPE Link. His freelance articles on spreadsheets have been published as far afield as Pakistan. During training sessions, you’ll often hear David state, “Either you work Excel, or it works you!”