There are different ways you can filter data in a Pivot Table in Excel. Show
As you’ll go through this tutorial, you’ll see there are different data filter options available based on the data type.
Types of Filters in a Pivot TableHere is a demo of the types of filters available in a Pivot Table. Let’s look at these filters one by one:
Note, there are two more filtering tools available to a user: Slicers and Timelines (which are not covered in this tutorial). Let’s see some practical examples of how to use these to filter data in a Pivot Table. Examples of Using Filters in Pivot TableThe following examples are covered in this section:
Filter Top 10 Items in a Pivot TableYou can use the top 10 filter option in a Pivot Table to:
Suppose you have a Pivot Table as shown below: Let’s see how to use the Top 10 filter with this data set. Filter Top/Bottom Items by ValueYou can use the Top 10 filter to get a list of top 10 retailers based on the sales value. Here are the steps to do this:
This will give you a filtered list of 10 retailers based on their sales value. You can use the same process to get the bottom 10 (or any other number) items by value. Filter Top/Bottom Items that make up a Specified Percent of the ValueYou can use the top 10 filter to get a list of top 10 percent (or any other number, say 20 percent, 50 percent, etc.) of items by value. Let’s say you want to get the list of retailers that make up 25% of the total sales. Here are the steps to do this:
This will give you a filtered list of retailers that make up 25% of the total sales. You can use the same process to get the retailers that make up the bottom 25% (or any other percentage) of the total sales. Filter Top/Bottom Items that make up a Specified ValueLet’s say you want to find out the top retailers that account for 20 million in sales. You can do this using the Top 10 filter in the Pivot Table. To do this:
This will give you a filtered list of top retailers that make up 20 Million of the total sales. Filter Items based on ValueYou can filter items based on the values in the columns in the values area. Suppose you have a pivot table created using retail sales data as shown below: You can filter this list based on the sales value. For example, suppose you want to get a list of all the retailers that have sales more than 3 million. Here are the steps to do this:
This would instantly filter the list and show only those retailers that have sales more than 3 million. Similarly, there are many other conditions that you can use such as equal to, does not equal to, less than, between, etc. Filter Data Using Label FiltersLabel filters come in handy when you have a huge list and you want to filter specific items based on its name/text. For example, in the list of retailers, I can quickly filter all the dollar stores by using the condition ‘dollar’ in the name. Here are the steps to do this:
You can also use wildcard characters along with the text. Note that these filters are not additive. So if you search for the term ‘Dollar’, it will give you a list of all the stores that have the word ‘dollar’ in it, but if you then again use this filter to get a list using another term, it will filter based on the new term. Similarly, you can use other label filters such as begins with, ends with does not contain, etc. Filter Data Using Search BoxFiltering a list using search box is a lot like the contains option in the label filter. For example, if you have to filter all the retailers that have the name ‘dollar’ in it, simply type dollar in the search box and it will filter the results. Here are the steps: This would instantly filter all the retailers that contain the term ‘dollar’. You can use wildcard characters in the search box. For example, if you want to get the name of all the retailers that start with the alphabet T, use the search string as T* (T followed by an asterisk). Since asterisk represents any number of characters, this means that the name can contain any number of characters after T. Similarly, if you want to get the list of all the retailers that end with the alphabet T, use the search term as *T (asterisk followed by T). There are a few important things to know about the search bar:
You May Also Like the Following Pivot Table Tutorials:
Can you filter a pivot table based on a cell value Google Sheets?If you filter by value then update your source data, you'll need to update your pivot table filter if you want that data to show in the pivot table. When you filter by condition, you can enter a value, a cell reference, or a field from your data. For example, if you select "Greater Than," you can enter 10, =Sheet1!
Can a pivot table filter based on cell value?You can filter items based on the values in the columns in the values area. You can filter this list based on the sales value. For example, suppose you want to get a list of all the retailers that have sales more than 3 million.
Can you custom filter in a pivot table?If desired, manually type the criteria by which you want to filter. You can also filter the row or column headings in a PivotTable by clicking the Row Labels or Column Labels list arrow and selecting only the value(s) you want to display. Select the item(s) you want to use as a filter. Click OK.
|