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

6 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.

  • I took a few of your courses and actually engaged you to do some work on a complex Excel file fior mer.

    I am trying to use the Ctrl +Enter feature you reviewed in your class but cannot find the text in my files.

    I am selecting the column I want to fill in, Fill & Select, Go to Special, selecting Blanks and then pressing Ctrl and the Enter key, but when I do nothing happens. My notes say to have the cursor in the column before you press Ctrl and Enter but when you do that the blank cells are no longer selected.

    • Hey, Dan!

      Yes, I remember you. There’s a bit of nuance to the technique that you’re trying to carry out. As you noted:

      1. Select range of cells that contain blank cells you wish to fill.
      2. Choose Finding & Select on Excel’s Home menu.
      3. Choose Go To Special.
      4. Double-click Blanks.

      Here’s the part that has you stuck:

      5. If your goal is to fill the blank cells with the data from the first cell that’s filled above them, type an equal sign, tap the Up arrow key, and then press Ctrl-Enter. If you instead wish to fill the blank cells with zeros, for instance, type a zero instead and then press Ctrl-Enter.

      The reason nothing was happening when you pressed Ctrl-Enter was because you hadn’t typed a formula or value in the active cell. It’s Ctrl-Enter that places that formula or value in all of the cells that have been selected.

      Thank you for attending my presentations, and for the project. Please let me know if you encounter any further difficulties with this technique.

      • In the presentation there was an example of doing this for a selected group of cells, or potentially, the entire worksheet.

        None the less, I got around this by inserting a zero in each blank cell, inserting a new column, and then wrote an if statement in that new column looking for the zero and, if present, copying the cell above the corresponding cell in the newly-created column. If no 0 just copy the cell.

        It worked but there is an issue with many of the cells which display as blank but were not populated with a zero; hence, the if statement did not work in those instances.

        I will send you an extract of the file so you can see some actual data. Look at rows 8, 12, 19, 24 for example. I need to populate columns E, F, G & H which are coming from columns D,J,L & M.

        Please look at this and tell me how to resolve this and send me an invoice.
        Thank you.

Leave a Comment