Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
Whenever you add a field to a PivotTable's column area or row area, you change the PivotTable structure by adding a layer of detail. But suppose you have a great PivotTable layout and want to filter the PivotTable using the values in a field that doesn't appear within the arrangement. For example, you might want to filter monthly sales by quarter, but without having the Quarter field change the PivotTable's layout. How do you do that? Well the answer is that you add the quarter field to the Report Filter field area and create the filters as normal.
So the first thing you do is click any cell new PivotTable and make sure that the PivotTable Field List task pane is displayed and in the bottom of the PivotTable Field List you see several different areas. There is the Column Labels area, the Row Labels area, and the Values area, which allow you to create the structure of the PivotTable. The area we haven't covered yet is called the Report Filter area. As the name implies you can add fields to that area without changing the structure of the interior of the PivotTable and create filters to limit the data that appears.
So let's say in this case that I wanted a filter based on the values in the Quarter field. To do that I drag the Quarter field down to the Report Filter Area and when I do, Excel asks if I want to replace the contents of the destination cells in Sheet 3. And the reason it is asking that is because the value Corporate Revenue appears in cell A1. Cells A2 and A1 are where the Report Filter area will go once I add a field to the PivotTable so the contents of cell A1 will be lost.
In this case, that's fine. I only put it there as an example, so I can click OK and when I do Excel creates the Report Filter area. Now to create a filter I can click the down arrow next to the Quarter field name and then the selection list is a little bit different than it is for when you create a value filter or a label filter based on a regular filed. In this case, if you want to select a single item, all you need to do is click that item. So let's say that I only want to see the results for quarter number 1.
To do that I click 1 and click OK and Excel displays only those values from January, February, and March of both years. However, let's that I want to show the results quarters 1 and 3. To do that, click the Filter arrow for the Quarter field and then check the Select Multiple Items box. When you do you see the familiar selection filter area that we have seen when we created other filters for this PivotTable. Simply select any box next to the item that you want to display.
In this case 1 and 3, and 4 is unselected. Click OK and now you see the results for quarters 1 and 3. If you want to clear a Report Filter you can just click the down arrow, click the All button, and then click OK. One nice thing about adding a Report Filter field is that you can create individual worksheets for each of the values in that field. So let's say for example that I wanted to create a worksheet that had only data for quarter 1, another for data with only quarter 2, and so on through quarter 4.
To do that make sure that you have a filed in the Report Filter area. You don't have to have a Report Filter applied at the time. It just needs to be a field in that area and then on the Options contextual tab, in the PivotTable group, click the Options button down arrow and then click Show Report Filter Pages. In the dialog box make sure that the quarter field appears and is highlighted and then click OK. When you do, Excel creates a new worksheet and each worksheet contains data for each value in the field that was in the Report Filter area.
So for example, we have quarter 1, which is on worksheet 1, and then worksheet 2 has the data for quarter 2, worksheet 3 number 3, worksheet 4 number 4. Now these are complete copies of the data. So for example, if I wanted to display all of the data on the 4 worksheet then I can click All, click OK, and all of the data appears. But when Excel creates the worksheet for you, it is filtered so it only displays the data for an individual item from the field in the Report Filter area.
If you have a worksheet with the same name as one of the field values, Excel names the new sheet value (2). So let's say for example, that there is already a worksheet named 4 in this workbook. In that case this worksheet would be named 4 (2). Filtering PivotTables using the Fields and Report Filter area is a powerful capability. Not only can you limit the data that appears in your PivotTable without changing its structure, you can create separate worksheets for each value in the Report Filter field.
Get unlimited access to all courses for just $25/month.Become a member
82 Video lessons · 97652 Viewers
80 Video lessons · 141103 Viewers
59 Video lessons · 59448 Viewers
52 Video lessons · 72766 Viewers
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.