When it’s time to crunch numbers in QuickBooks, most users rely on reports within the software, while others turn to analytics programs. In either case, you may still sometimes need to look at your accounting data in new ways. In such cases, pivot tables can make quick work of sussing out patterns or issues with your data that might be hard to discover otherwise. In Excel, pivot tables allow you to create instant reports and/or charts by simply dragging and dropping fields with your mouse. This means you can quickly rearrange even massive reports into interactive, yet compact summaries.
As with any accounting package, the first challenge is always to get data out of QuickBooks and into Excel. If you’re using an analytics program, you can likely skip ahead to the Data Preparation in Excel section once you export your data to a spreadsheet. If you’re going to work strictly within QuickBooks, I’ve previously shared my favorite report to use when analyzing data, but you’re not limited to just that report. You can usually whip just about any transaction listing into a pivot table–ready format with a few minor changes. Let’s use the Sales by Customer Detail report as an example:
- In QuickBooks, select Reports, Sales, and then Sales by Customer Detail.
- As shown in Figure 1, by default, this report groups transactions by customer, which isn’t suitable for pivot table analysis. A couple of minor changes can fix that:
- Click Customize Report (or Modify Report in QuickBooks 2011 and earlier).
- As shown in Figure 2, for this exercise, choose This Fiscal Year from the Dates field, and then change Total By to Total Only.
- Unclick the Left Margin and Balance columns so that you don’t have to manually delete these extraneous columns in Excel.
Figure 1: Most QuickBooks reports total by account, customer, vendor, etc., and aren’t readily suitable for pivot table analysis.
Figure 2: Simple customizations can transform most QuickBooks reports into an Excel pivot table–ready format.
At this point, your report should look similar to Figure 3, so the next step is to send the report to Microsoft Excel. As I’ve noted previously, most users click Excel, Create New Worksheet, and then click Export. This is a perfectly valid approach, but you may find yourself waiting for several minutes for the report to get to Excel. My preferred alternative is to save the document as a CSV file that I then open manually in Excel.
Figure 3: This format is much cleaner than the same data presented in Figure 1.