Practice Management Small Business Tech Trends

Analyze QuickBooks Data with Excel Pivot Tables

Written by David Ringstrom

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.

QuickBooks reports not readily suitable for pivot table analysis

Figure 1: Most QuickBooks reports total by account, customer, vendor, etc., and aren’t readily suitable for pivot table analysis.

You can customize QuickBooks reports easily

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.

Cleaner format

Figure 3: This format is much cleaner than the same data presented in Figure 1.


Save pagePDF pageEmail pagePrint page

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!”

2 Comments

  • David

    Is there a way to delete the total rows before exporting into excel? I’m trying to create a report that is completely pivot table ready.

    Thank you,
    Orry

    • Unfortunately QuickBooks won’t allow you to remove the total row, and will often include a blank row beneath the titles and a blank column at the right. Some users rely on tools like QODBC to pull data directly from QuickBooks into Excel, but isn’t always as simple as it seems it should be. The most automated way to create a pivot table report is to use Microsoft Query to connect to the QuickBooks file that you create, because Microsoft Query will allow you to filter out rows or columns that you don’t want based on criteria that you specify. Send me an email to david@acctadv.com and I’ll be glad to send you a couple of PowerPoint slides that document getting started with Microsoft Query.

Leave a Comment