Filter a Pivot Table

Label Filters & Value Filters

Label & Value Filters are the in-built filters in a pivot table. The only thing that differentiates both is that you can filter your data based on numbers i.e., values using value filters and text using label filters.

In simple words, if you are filtering numbers, you should use value filters and label filters while playing with text. You will find some interesting options when you click on value/label filters. Some of the options are common except top 10, which you will find in value filters.

1. Equals: You can filter your data based on its value with an exact match, it’s like an exact match to filter values.
2. Does Not Equal: It will filter all the values which do not contain the values you mention. It’s exact opposite from the earlier option.
3. Begins With: This will filter data beginning with the specific text you mention.
4. Does not Begin with: This will help you to filter all the data which does not begin with the specific text you mention.
5. Ends with: As the name specifies, it filters the data which ends with a specific text.
6. Does not Ends with: This is exactly the opposite of “Ends with”. This will filter all the data which does not end with the mentioned string.
7. Contains: Now this helps you to filter the data which contains specific text/word in the middle, beginning, or the end.
8. Does not Contain: This helps you to filter the data which does not contain specific text/word.
9. Greater than:  This is a value filter and will filter all the data which is greater than the value you mention.
10. Greater than or Equal to: This is a value filter and will filter all the data which is greater than or Equal to the value you mention.
11. Less than: This is a value filter and will filter all the data which is Less than the value you mention.
12. Less than or Equal to: This is a value filter and will filter all the data which is less than or Equal to the value you mention.
13. Between: This is helpful when you want to apply a filter between ranges. For example, you need to filter the industries with cost prices between 100-150.
14. Not Between: This is helpful when you want to apply a filter for some specific values.
15. Top 10: When you need to filter top 10 or bottom 10 values in your pivot, you need this filter. This will only show the number of values you choose that fit your criteria.

Using Report Filter

Report filters are amazing when you need to filter data based on two or more criteria. To add a column to report filters.

• First, click anywhere on the pivot table and activate the field list option.
• Now, select the column which you want to add to report filters. Here we will add industry.
• Here drag/add the column Industry to filters in pivot table fields.
  
• Now the pivot table will look like this.
  
• After this click on the little drop-down next to industry.
  
• Here you will get a list of the industries in your data.
• Now, to select multiple, tick mark on the bottom “Select multiple”.
  
• In the end, if you have a long list, you can simply type in the search box. Here we are typing “Med”, and all the industries with “Med” text are filtered.
  
• Here, you can deselect or select the industries you want to filter.

Search Box

Search box is the easiest and most handy filter.

• First, click inside the search box.
  
• Here start typing text to filter the data.
  
• After this, you will see that the data is filtered by the text “ENT”. Here you will notice that all the items which have “ENT” are listed below.
• Untick the items you do not need.
  
• Hit OK.

Clear Filter from a Row or a Column

Method 1

• Click on the row/column where you have applied the filter and right-click.
  
• Here look for the option of “Filter” and click to get the option of “Clear Filter from Name”.
  

Method 2

• Another way of doing this is simply to click on the arrow next to the column “Name”.
  
• Now click on the “Clear Filters from “Name””.
  

Clear All Filters

In case you want to clear all the filters irrespective of columns and rows, follow the below steps.

• To start with, click on the pivot table to activate analyze tab.
  
• Now, click on the little down arrow next to clear.
  
• At last, click on “Clear Filters”. This will clear all the filters applied to the pivot table (in other words, it will reset the filters).