A QuickBooks user recently commented on one of my articles for this blog that it is “very difficult to get marketing data (from QuickBooks desktop) i.e. sales by state. I’ll be the first to admit that many times accounting transactions have the appearance of being trapped under glass in QuickBooks, but there’s always a way to get what you want. In this article I’ll describe two different techniques you can use to tally QuickBooks customer sales by city or state based on a report exported to Excel.
The first challenge with QuickBooks is always to pick the correct report. For instance, you might try choosing Reports, Sales, and then Sales by Customer Summary. When you click the Customize Report button you’ll find that you cannot add or remove columns from this report, as shown in Figure 1. However, if you choose Sales by Customer Detail, you can choose the columns you wish to appear on the report. Further, we’ll see how QuickBooks 2015 makes it far easier customize reports.
Figure 1: The Sales by Customer Summary report does not allow you to add or remove columns.
Carry out these steps to create a Sales by Customer Detail report:
- Choose Reports, Sales, and then Sales by Customer Detail.
- Click the Customize Report button.
- To generate our report we’ll only need 4 fields – it’s easy to make these selections in QuickBooks 2015, as shown in Figure 2:
- Name City
- Name State
- Click the Checkmark button twice to group all fields presently on the report together. Turn off the following fields:
- (left margin)
- Sales Price
- Scroll down a little further and turn these two on:
- Name City
- Name State
In QuickBooks 2014 and earlier you’ll have to expend a bit more effort, as the field list is only scrollable but not sorted in any fashion.
- Click once on the first field in the list, which is (left margin), and then use the down arrow key to move down through the list. Tap the space bar each time you find a field you want to toggle on or off.
- In all versions of QuickBooks change the Total By field from Customer to Total Only and then click OK.
Figure 2: Follow these steps to customize your report.
At this point your report should look similar to Figure 3. Carry out the following steps to export the report to Excel:
- Click the Excel button, choose Create New Worksheet, and then click Export.
Figure 3: Follow these steps to export your customized report.
- Once the report appears in Excel delete row 2 and column A, so that the report looks similar to Figure 4.
- Next scroll down and delete the Grand Total row from the bottom of the report. To quickly reach the bottom of a report like this in Excel, you can also click on column A and the press Ctrl-Down Arrow. Assuming that column A doesn’t have any blank cells, you’ll quickly zoom to the bottom of the report.
Figure 4: Once the report is exported to Excel delete column A, row 2 and the Grand Total row.
At this point you’re ready to implement the first of the two techniques that I’ll be demonstrating, which is Excel’s Subtotal feature. Before we implement this feature, we must first sort the data in the desired order. Since I’ve chosen to use one of the sample companies in QuickBooks all of my customers are located in California, so I’ll subtotal the sales by city. To do so, right-click on cell B2—which should contain a city name—then choose Sort, and then Sort A to Z.