Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
PivotTables help you summarize large amounts of data, but you can always limit the data that appears by creating a filter. If the data you want to display in your PivotTable fits the rule, such as all values greater than 1000, you can define that rule and use it to filter your PivotTable. To filter a PivotTable using the values in the field, you can click any cell in the body of the PivotTable and then go over to the PivotTable Field List task pane and position your mouse pointer over the field that you want to filter by. In this case, I want to filter by month, so I will move the mouse pointer over that field, and then on the right edge click the down arrow that appears and you can select the type of filter that you want to create.
You can create two different types of filters: a label filter or a value filter. As the name imply,s a label filter lets you filter on the values in the label area. So for example, in this PivotTable we have Years, 2009, Quarters, 1 2 3 and 4, and then Month, January, February, March and so on. If we want to filter based on one of those label values, we would create a label filter. However, if we wanted to create a filter based on the values within the data area of the PivotTable, then we can create a value filter.
So for the Month field, I will create a value filter and then I can select the type of filter that I want to create. So in this case I want to display any month that has a total of more than $200. So I will click Greater Than and then in the Value Filter dialog box, I have Sum of Revenue is greater than, and then I can type in my criteria, which is 200. When I click OK, Excel applies the filter to the PivotTable and you can see that it only displays rows where the grand total is more than 200.
To remove the filter, I can go back into the PivotTable Field List, click the Month header, and then click Clear Filter from Month. Now let's say that I want to filter based on label values and for this example I want to display only those values for Quarters 3 and 4, in other words Quarters with a value greater than 2. To do that I will filter based on the Quarter label. So back in the PivotTable Field List, I will position my mouse pointer over the Quarter field, click the down arrow, point to Label Filters and then click Greater Than.
I want to display any Quarter that is greater than 2 so I will type 2 in the second box and click OK. When I do, Excel filters the PivotTable so it only displays results from Quarters 3 and 4. Filtering PivotTable data helps you gain insight into your data by focusing the display on the values you want to examine. You should experiment with the many types of filters available to you, so you will know which to use to answer particular questions about your data.
Get unlimited access to all courses for just $25/month.Become a member
82 Video lessons · 64848 Viewers
80 Video lessons · 124392 Viewers
52 Video lessons · 60319 Viewers
59 Video lessons · 46142 Viewers