QuickBooks

Filtering and Slicing QuickBooks Data in Excel – Part 2

Written by David Ringstrom

In Part 1 of this series I compared using Excel’s Filter and Slicer features to manage the amount of data that you see at once in a list. Filtering is available in all versions of Excel, while the ability to use Slicers with Excel’s Table feature was introduced in Excel 2013. In this article I’ll turn my attention to using Slicers to control the data that you view onscreen in PivotTables. This functionality is available in Excel 2010 and later.

If you need to get up to speed on using Pivot Tables in Excel, I’ve written about the basics previously. For those who want to go deeper, you can see how to use Pivot Tables to tally QuickBooks customer sales by city or state, as well as more specialized needs such as tallying QuickBooks purchase orders by user. Let’s now build on your experience with Pivot Tables by summarizing a Custom Transaction Detail report from QuickBooks. The steps shown in Figure 1 assume that you’ve carried out all of the steps in Figure 1 through Figure 5 from part 1 in this series.

At this point your Custom Transaction Detail report should appear within a workbook as a Table. The Table feature enhances our ability to work with lists, while the Pivot Table feature allows you to quickly summarize such lists into meaningful reports. It can also make adding formulas easier. For instance, let’s add a new Amount column to our table that nets debits and credits together, as shown in Figure 1:

  1. Click on the next available cell in row 1, in this case M1.
  2. Type the word Amount and press Enter.
  3. Type the following formula: =J2-K2 and press Enter. Note that your formula may appear as =[@Debit]-[@Credit] if you navigate to those cells with the arrow keys or your mouse instead of directly typing the formula out. Either is acceptable.
Filtering and Slicing QuickBooks Data in Excel

Figure 1: Tables eliminate the need to copy formulas down a column as the entire column of formulas updates automatically.

Now let’s base a Pivot Table off this list, as shown in Figure 2:

  1. Click any cell within your Table.
  2. Activate Excel’s Design The Design menu will not be available if you’ve presently selected a cell outside of a table, or if you have not yet transformed your list into a Table.
  3. Choose Summarize with Pivot Table.
  4. Click OK within the Create PivotTable
Custom Transaction Detail Report

Figure 2: Taking a moment to make a list into a Table before creating a Pivot Table eliminates a spreadsheet risk.

At this point we have the beginnings of a Pivot Table, to which we’ll add a couple of fields:

  1. Check the Type checkbox from the PivotTable Field
  2. Check the Amount checkbox from the PivotTable Field
  3. Right-click on the words Sum of Amount in the Pivot Table.
  4. Choose Number Format.
  5. Choose Number.
  6. Click Use 1000 Separator (,).
  7. Click OK.
  8. Your Pivot Table should look like Figure 3.  At this moment the amounts are all zero because we’ve netted debits and credits together. Later in this article we’ll filter the pivot table by class and amounts will then appear in the pivot table.
Pivot Table

Figure 3: Carry out these steps to build a Pivot Table from the Custom Transaction Detail report.

Let’s turn our attention away from the Pivot Table for a moment so that I can offer some additional background about Excel’s Table feature. Although making your data into a Table before you create a Pivot Table may feel like an extra step, doing so eliminates an important risk within your Pivot Tables. Ostensibly you could clean up any QuickBooks report and then jump right into creating a Pivot Table. However, if you were to later append any data to the bottom of your list, you’d have to manually resize the Pivot Table to have the additional data appear.

As shown in Figure 4:

  1. Click any cell within your Pivot Table.
  2. Activate the first PivotTable Tools menu (Analyze in Excel 2013 and later, Options in Excel 2010 and earlier).
  3. Click Change Data Source.
  4. Select the expanded data range, which will include the new data you pasted at the bottom of your list.
  5. Click OK.

At this point any new data would appear on the Pivot Table. Conversely, if your Pivot Table is based on a Table, any new data will appear once you refresh your Pivot Table. Remember, Pivot Tables work off of a snapshot in time of your data, so whenever you change the source data in any way, such as appending new data, updating information within the list, or adding new calculation columns to your table, none of this will appear until you right-click on the Pivot Table and choose Refresh. You can also find both Refresh and Refresh All commands on either Excel’s Data menu or the first PivotTable Tools menu in your version of Excel.

Pivot Table Tools

Figure 4: The Table feature eliminates the need to manually resize a Pivot Tables’ source data range.


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

Leave a Comment