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:
- Click on the next available cell in row 1, in this case M1.
- Type the word Amount and press Enter.
- 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.
Now let’s base a Pivot Table off this list, as shown in Figure 2:
- Click any cell within your Table.
- 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.
- Choose Summarize with Pivot Table.
- Click OK within the Create PivotTable
At this point we have the beginnings of a Pivot Table, to which we’ll add a couple of fields:
- Check the Type checkbox from the PivotTable Field
- Check the Amount checkbox from the PivotTable Field
- Right-click on the words Sum of Amount in the Pivot Table.
- Choose Number Format.
- Choose Number.
- Click Use 1000 Separator (,).
- Click OK.
- 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.
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:
- Click any cell within your Pivot Table.
- Activate the first PivotTable Tools menu (Analyze in Excel 2013 and later, Options in Excel 2010 and earlier).
- Click Change Data Source.
- Select the expanded data range, which will include the new data you pasted at the bottom of your list.
- 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.