Join Curt Frye for an in-depth discussion in this video Filtering a PivotTable using report filter fields, part of Excel for Mac 2011: Pivot Tables in Depth.
- [Voiceover] Whenever you add a field to a pivot table's column area or row area, you change the pivot table's structure by adding a layer of detail. But suppose you have a great pivot table layout and want to filter the pivot table using the values in a field that doesn't appear within that arrangement. For example, you might want to filter monthly sales data by quarter but without having the quarter field change the pivot table's layout. If you want to create a filter, without changing the layout of your pivot table, you can do so by moving a field to the report filter area.
I'll show you how to do that in this movie. My sample file is the Report Filter Excel workbook which you can find in the Chapter three folder of your exercise files collection. I have displayed a pivot table and it's monthly sales for 2014 and 2015 for four different room types. And let's suppose that I only want to display values for a particular quarter, such as quarter #2. To do that, rather than adding quarter to the row labels area between the year and month, which would change the organization of the pivot table, I can drag it from the field list to the report filter area and you can see that the field has been added in cell A1 and in cell B1, there a control that I can click to control which values appear.
If you have data in cell A1 or B1, then Excel displays an indicator dialog box asking if you want to delete the data. If you click yes, then it deletes the data and displays the field as you see it here. If not, it leaves the data and moves the quarter field from the report filter area back up to the field list. But in this case, I didn't have any data there so that didn't happen. I'll click the report filter header button here and you see that I can create a filter.
I can create a search filter if I want, but what I want to do now is create a selection filter. So I will clear the select all check box; that removes all the data and if I want to show only those values for quarter #2, I can click 2 and move quarter over to the side. You can see those values appear there: April, May, and June. If I want, I can add other quarters, such as quarter #4, and I can remove them such as by clearing quarter #2. When I'm done, I can either click select all again or I can click clear filter.
And when I'm done with this dialog box, I can "x" out to close it. Filtering of pivot tables using fields and the report filter area is a powerful capability. By using them, you can limit the data that appears in your pivot table without changing its structure.
- Creating a PivotTable
- Refreshing PivotTable data
- Managing totals
- Creating calculated fields
- Grouping PivotTable fields
- Sorting and filtering PivotTable data
- Printing PivotTables