Filtering Grand Total Amounts Within Excel Pivot Tablesby
Anyone who has worked with pivot tables has likely found it easy to filter rows or columns within the pivot table, and as you’ll see in this article, you can indeed filter any element of a pivot table that you wish.
When you click the arrow within the Row Labels or Column Labels sections, a drop-down menu lets you choose what you’d like to see. At first glance, it may appear that you cannot filter the Grand Total column or row within a pivot table.
To build a pivot table in Excel you must start with a list, as shown in Figure 1. Make sure that you have a unique title at the top of each column, and that the title is comprised of a single row. As shown in Figure 2:
- Click any cell within the list.
- Activate the Insert menu.
- Choose the Pivot Table command.
- Click OK.
- When the new pivot table appears, click any text-based field to add it to the Rows section, such as Vendor.
- Drag any text-based field to the Columns section, such as Product.
- Click amount-based field to add it to the Values section.
Figure 1: Ideal data for a pivot table is in a list form such as this.
Figure 2: Transforming a list into a pivot table report only requires a handful of mouse clicks.
You’re now ready to see how to filter data within the pivot table. For instance, let’s say that we wish to only see sales of apples and oranges, as shown in Figure 3.
- Click the arrow in the Column Labels field.
- Uncheck the checkboxes for all items except Apples and Oranges.
- Click OK.
- The pivot table only displays columns for Apples and Oranges.
Figure 3: The pivot table allows you to filter for specific columns.
You can filter rows in a similar fashion, as shown in Figure 4:
- Click the arrow in the Row Labels field.
- Type the word Fruit in the Search Box (or manually filter in Excel 2007 and earlier).
- Click OK.
- The pivot table now only has rows for vendors that have the word Fruit in their name.
Figure 4: The pivot table allows you to filter for specific columns.
As shown in Figure 5, we can reset the pivot table to show all the data again:
- Activate the Analyze menu in Excel 2013 or later, or the Options menu in Excel 2010 and earlier. This menu is only available when your cursor is within the pivot table.
- Choose the Clear command.
- Choose Clear Filters.
- The pivot table now shows all the data.
Figure 5: Reset the pivot table to view all the data again.
Now that you’re grounded on filtering within a pivot table, let’s now filter the grand totals, as shown in Figure 6:
- Assuming you wish to filter the Grand Total column, click the arrow in the Row Labels field.
- Choose Value Filters.
- Select an option from the submenu, such as Greater Than.
- Enter an amount, such as 300000.
- Click OK.
- The pivot table is filtered to show only grand total amounts greater than 300,000.
Figure 6: You can also filter the pivot table by the Grand Total column.
Filtering the Grand Total row works in the same fashion, except you’ll use the Value Filters command from the menu in the Column Labels field.
David H. Ringstrom, CPA, is an author and nationally recognized instructor who teaches scores of webinars each year. His Excel courses are based on over 25 years of consulting and teaching experience. His mantra is “Either you work Excel, or it works you.” David offers spreadsheet and database consulting services nationwide.