If you create a pivot table, you will notice that there is a little icon that allows you to filter data in rows, but you can’t find the same icon to filter values.
For this example, we are going to use the following table.
Insert a pivot table and check Director and Actor in PivotTable Fields.
You should have this configuration.
Your pivot table should look like this.
As you can see, there is a triangle icon next to row labels, but there is none in Count of Actor. You can get access to PivotTable value filter by right-clicking inside a PivotTable and choosing Filter.
Here, you can find a few options.
Top 10 Filter
Keep only selected items – All the rows you selected before right-clicking will stay, and the other will become hidden.
Hide selected items – This option is opposite to the previous one.
Top 10 – Gives you the top 10 results. The default option won’t work in our case because we don’t have enough rows. If you click this option, you will notice that there are much more options, like:
- the number of top items,
- instead of the top, you can choose the bottom,
- instead of items, you can choose a percentage or sum.
This option will filter by the row name. In this case the names of directors. Here, you can use wildcard characters.
In our case, the value filter will filter data by the number of actors that played in a movie made by a director.
This filter offers the following options:
- does not equal,
- is greater than,
- is greater than or equal to,
- is less than,
- is less than or equal to,
- is between,
- is not between.
Filtering values will help you to reduce long lists of data without changing a table.