QuickBooks

Filtering and Slicing QuickBooks Data in Excel – Part 2

Filtering versus Slicing Pivot Tables

Now, let’s return to our Pivot Table and compare filtering versus slicing. Let’s say that we want to view the sum of the transactions by class, as shown in Figure 5:

  1. Drag Class from the Pivot Table Field List into the Filters section of the PivotTable Field
  2. Click the arrow in cell B2.
  3. Choose New Construction.
  4. Click OK.
  5. At this stage the Pivot Table shows the transaction amounts by type.
Pivot Table Field List

Figure 5: Although you can filter a Pivot Table from the PivotTable Field list, it has its limitations.

At this moment, we can see transactions for New Construction, but what may blur for us is what other classes aren’t presently shown. To determine this we must click the arrow in cell B1 again to extend the filter menu down. Repetitive actions like this in Excel often lead to fatigue and simply overlooking key data elements. Fortunately, Excel’s Slicers feature can solve this for us:

  1. Click on any cell within the Pivot Table.
  2. Choose Slicer from Excel’s Insert
  3. Choose Class from the list.
  4. Click OK.
  5. Notice that the Slicer indicates that New Construction is presently shown on the Pivot Table.
  6. Choose Overhead from the Slicer.
  7. The filter in cell B1 also reflects Overhead.
  8. The Slicer shows at a glance the types that are presently hidden from the Pivot Table.
  9. Click the Clear button on the Slicer to show all fields again.
Slicer Feature

Figure 6: Excel’s Slicer feature filters the data and allows the user to quickly determine which classes are hidden at a given time.

Not every field will necessarily lend itself to being easily sliced. For instance, let’s create a Slicer for the Name field:

  1. Click on any cell within the Pivot Table.
  2. Choose Slicer from Excel’s Insert
  3. Choose Name from the list.
  4. Click OK.
Slicer Feature

Figure 7: Not every field is a good candidate for the Slicer feature.

You can now slice based on Class and/or Name. However, notice that the Name field contains dozens of entries, and unlike the Filter drop-down menus, there’s no Search field that you can use to winnow the list, so you must manually scroll up and down the list to slice on a given Name.

Slicing Multiple Pivot Tables

Now let’s add a second Pivot Table so that I can show you how a Slicer can control more than one Pivot Table at once. This certainly puts slicing in a class above filtering, as filtering must be done manually, one Pivot Table at a time. To create a second Pivot Table:

  1. Activate the worksheet that contains the Table, as shown in Figure 8.
  2. Choose Summarize With Pivot Table from the Design
  3. Choose Existing Worksheet.
  4. In the Location field select cell D3 from the worksheet that contains your existing Pivot Table.
  5. Click OK.
  6. Choose Account from the Pivot Table Field list, as shown in Figure 9.
  7. Choose Amount from the Pivot Table Field
  8. Right-click on the Class Slicer.
  9. Choose the Connections command, which is labeled Report Connections in Excel 2013 and later, or PivotTable Connections in Excel 2010.
  10. Select your second Pivot Table.
  11. Click OK.
Create Pivot Table

Figure 8: Placing two Pivot Tables on the same sheet sets the stage for Slicers to control multiple Pivot Tables.

 

Pivot Table Fields

Figure 9: A Slicer can control more than one Pivot Table at a given time.

Repeat those steps for the Name Slicer if desired. You’ll now see that both Pivot Tables adjust automatically based on selections that you make from a Slicer that is connected to both Pivot Tables.

If you’re using Excel 2013 or later, an alternative to Slicers is the Timeline feature that I’ve discussed previously. This offers functionality akin to Slicers, but allows you to filter based on a variety of date ranges.


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