Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
PivotTables can summarize huge data collections, but many times you'll want to limit the data displayed in a given category. For example, if your company's operations are divided into four regions, you might want to display just those results for one of the regions. You can limit the data displayed in a PivotTable by creating a filter. Earlier in this course, I showed you how to filter a data list and also an Excel table. You can use those skills to create selection filters, and also to filter by rule. The skills are exactly the same. In this movie, I'm going to show you a way of filtering that is unique to a PivotTable, and that is by using the Report Filter field.
If you look over in the PivotTable Builder, you'll see that there are four areas: the Values area, which is the interior of the PivotTable; Row Labels, which provides the rows labels here; Column Labels, which provides structure and value for the columns of your PivotTable; but we haven't talked about the Report Filter area, up at the top. And the reason we haven't is because the Report Filter area, when you add a field there, it doesn't affect the interior of the PivotTable unless you create a filter based on one of those fields.
So let's say, for example, that I wanted to move the Region field header up to the Report Filter area. When I do that, I'm removing Region from the Row Labels area, so that means that I did change the structure, but now let's see what happens if I put Region back. Actually, I'll put it above Month, so it was the same as it was before. To use the Region field in the Report Filter area, I would drag Region from the Row Labels area to Report Filter. So now I can use that Region field here to filter the body of the PivotTable without affecting the structure any more than removing Region did.
To create a filter based on the Report Filter field, you click the field's filter arrow. So, you can just type in value here. So, for example, if I wanted to filter by East, I can type in E, but the word West also contains an E. So I need to type an A, and now if I were to apply the filter at this moment, I would only see values for East, but I'll backspace over that to display everything and instead use the selection. First I clear Select All, and then I will click North and South.
So now the data in the PivotTable reflects only those values from North and South, even though the regions and the region names appear nowhere in the body of the PivotTable, and that is the beauty of using the Region Filter area. If you want to clear the filter, you can just click Clear Filter and to get rid of the Region Filter dialog, you can click the close button, and away it goes. Filtering your PivotTable data gives you pinpoint control over the values that appear in your PivotTable. The more you narrow down the data you display, the easier it is to find the data you need.
Get unlimited access to all courses for just $25/month.Become a member
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.
Your file was successfully uploaded.