While working in Excel, we often need to use Pivot Tables. Using the Filter option in Pivot Tables is a great way to summarize data. We can filter our data in Pivot Tables in a number of ways. In this article, we will learn six simple yet effective methods to filter Pivot Table based on cell value in Excel. So, let’s start this article and explore these methods.
Excel Pivot Table Filter Based on Cell Value: 6 Handy Examples
In this section of the article, we will learn six effective ways to filter Pivot Table based on cell value in Excel. For instance, let’s say we have the Laptop Prices in the Micro Center Store as our dataset. Here, we will use various filters based on cell values to filter the Pivot Table made by using these data.
1. Filtering Pivot Table Using Search Box Option
Using the Search Box option is one of the easiest ways to filter Pivot Table based on cell value in Excel. To do this, let’s follow the steps outlined below.
Step 01: Create Pivot Table
Before doing anything, we need to create a Pivot Table using our dataset. Let’s use the steps discussed in the following section to create a Pivot Table.
- Firstly, select the entire dataset and go to the Insert tab from Ribbon.
- After that, select the PivotTable option from the Tables group.
As a result, the PivotTable from table or range dialogue box will appear on your worksheet.
- Now, in the PivotTable from table or range dialogue box, choose the New Worksheet option.
- Then, click OK.
Consequently, the PivotTable Fields dialogue box will be available as shown in the following image.
- In the PivotTable Fields dialogue box, drag the Product field to the Rows section.
- Following that, drag the Price field to the Values section.
As a result, you will have the following Pivot Table on your worksheet.
Step 02: Apply Filter Option in Pivot Table
- After that, click on the filter button as marked in the image below.
- Then, click on the Search Box and type the text based on which you want to filter the Pivot Table. Here, we typed “Acer” in the Search Box.
- Finally, click OK.
Consequently, the Pivot Table will be filtered with all the Products that have Acer in it.
Read More: Excel VBA to Filter Pivot Table Based on Cell Value
2. Using Text Value to Filter Pivot Table
In this section of the article, we will filter the Pivot Table based on cell value as a text. To be precise, we will use the Label Filters option here to do this. In the Label Filters option, there are a lot of criteria to filter the Pivot Table. We will try to cover these different options here.
2.1 Finding Values in Pivot Table Based on Exact Text Value
Suppose, we want to filter the Pivot Table to a specific text. To do this, we need to know that specific text correctly. Approximate matching will not work using this option. Now, let’s follow the instructions outlined below.
Steps:
- Firstly, follow the steps mentioned in Step 01 of the first method to create a Pivot Table.
- After that, click on the filter button as shown in the image below.
- Then, choose the Label Filters option.
- Subsequently, select the Equals option.
As a result, a dialogue box named Label Filter (Product) will appear on your worksheet.
- Now, enter the filter text in the marked field as shown below. Here, we inserted “Lenovo IdeaPad 320-15IKB”.
- Lastly, click OK.
Consequently, the Pivot Table will be filtered based on the text that you used as demonstrated in the following picture.
Read More: How to Hide Filter Arrows from Pivot Table in Excel
2.2 Filtering Based on Text Values That Begin with Specific Text
Now, we will filter the Pivot Table based on text values that begin with a specific text. For example, in the dataset, we have multiple Products that are from Asus. But their model numbers are different. Let’s say we want to get all the Products from Asus. Now, let’s follow the steps mentioned below to do this.
Steps:
- Firstly, use the steps outlined in Step 01 of the first method to create a Pivot Table.
- Following that, click on the filter button as shown in the image below.
- Now, select the Label Filters option.
- Then, select the Begins With option.
As a result, a dialogue box named Label Filter (Product) will appear on your worksheet.
- Now, enter the beginning text. In this case, we used “Asus”.
- Finally, click OK.
Subsequently, all the Products from Asus will be available as shown in the image below.
2.3 Finding Values Based on Text Values That End with Specific Text
Now, we will filter the Pivot Table based on text values that end with a specific text. For instance, let’s say we want to show the Products that have “Pro” at the end of their name. So, let’s apply the following steps to do this.
Steps:
- Firstly, apply the steps discussed in Step 01 of the first method to create a Pivot Table.
- Now, click on the filter button as shown in the image below.
- After that, choose the Label Filters option.
- Subsequently, select the Ends With option.
Consequently, a dialogue box named Label Filter (Product) will appear on your worksheet.
- Now, enter the ending text. In this case, we used “Pro”.
- Finally, click OK.
As a result, you will have the list of the Products that have “Pro” at the end of their name.
2.4 Filtering Based on Text Values That Contain Specific Text
In this section of the article, we will learn how we can filter a Pivot Table based on a text value that has a specific text inside it. Let’s say, we want to show only the Product that has a text called “Swift” in it. So, let’s use the instructions outlined below to do this.
Steps:
- Firstly, use the procedure mentioned in Step 01 of the first method to create a Pivot Table.
- Following that, click on the filter button as shown in the image below.
- Afterward, choose the Label Filters option.
- Then, select the Contains option.
Subsequently, a dialogue box named Label Filter (Product) will appear on your worksheet.
- Now, enter the text that should be present inside the Product name. In this case, we used “*Swift*”.
- Finally, click OK.
Note: Here, we used Wildcards to define the containing text. “*Swift*” means that any text can be present before and after the word Swift.
Consequently, you will have the following output on your worksheet as shown in the image below.
3. Filtering Pivot Table Based on Dates
In the previous section, we discussed how we can filter Pivot Table based on cell value as a text. Now, we will learn to filter using Pivot Table based on the date range in Excel. To do this, we need to rearrange the Pivot Table Fields while creating the Pivot Table.
3.1 Finding Values in Pivot Table Based on Exact Date
Here, we will use an exact date to filter our Pivot Table. So, let’s follow the steps outlined below to do this.
Step 01: Insert Pivot Table
- Firstly, follow the steps mentioned in Step 01 of the first method to create a Pivot Table.
- While defining the PivotTable Fields, drag the Order Data field to the Rows section and the Price field to the Values section.
As a result, you will have the following Pivot Table on your worksheet.
Step 02: Define Date Filter
- After that, click on the filter button as shown in the image below.
- Then, select the Date Filters option.
- Subsequently, click on the Equals option.
As a result, a dialogue box named Date Filter (Order Date) will appear on your worksheet.
- Following that, enter the date based on which you want to filter the Pivot Table. In this case, we used the date 8/11/2022.
- Lastly, click OK.
Consequently, the Pivot Table will be filtered for only the date 8/11/2022 as demonstrated in the following image.
3.2 Filtering Pivot Table Before Specific Date
Let’s say we want to get the data before a specific date from a Pivot Table. Let’s learn how we can do this by using our dataset. So, follow the instructions explained in the following section.
Steps:
- Firstly, follow the steps mentioned in Step 01 of the first method of this section to insert a Pivot Table.
- Following that, click on the filter button as shown in the image below.
- Afterward, select the Date Filters option.
- Then, choose the Before option.
Subsequently, a dialogue box named Date Filter (Order Date) will be available on your worksheet.
- After that, enter the date based on which you want to filter the Pivot Table. In this case, we used the date 11/1/2022.
- Finally, click OK.
Consequently, the Pivot Table will show the “Order Dates” that are before the date 11/1/2022 as shown in the following picture.
3.3 Finding Values in Pivot Table After Specific Date
In this section of the article, we will filter our Pivot Table after a specific date. So, let’s use the steps explained in the following section to do this.
Steps:
- Firstly, follow the steps mentioned in Step 01 of the first method of this section to insert a Pivot Table.
- Then, click on the filter button as shown in the following picture.
- After that, choose the Date Filters option.
- Now, select the After option.
Following that, a dialogue box named Date Filter (Order Date) will be available on your worksheet.
- Next, enter the date based on which you want to filter the Pivot Table. In this case, we used the date 11/1/2022.
- Lastly, click OK.
As a result, the Pivot Table will display the “Order Dates” that are after the date 11/1/2022 as demonstrated in the image below.
3.4 Filtering Pivot Table Between Two Dates
Suppose, we want to filter our Pivot Table so that we can show data that are between two specified dates. For demonstration, we will use the dataset we gave in this article. So, let’s follow the procedure mentioned below to do this.
Steps:
- Firstly, follow the steps mentioned in Step 01 of the first method of this section to insert a Pivot Table.
- Following that, click on the filter button as shown in the following image.
- Then, choose the Date Filters option.
- After that, select the Between option.
Subsequently, a dialogue box named Date Filter (Order Date) will be available on your worksheet.
- Now, insert the starting date and the ending date, based on which you want to filter the Pivot Table. In this case, we used the dates 8/1/2022 and 10/31/2022 as our starting date and ending date, respectively.
- Finally, click OK.
Consequently, you will have the “Order Dates” between the starting date and the ending date as shown in the following image.
Read More: Excel VBA Pivot Table to Filter Between Two Dates
4. Utilizing Numerical Value for Filtering Pivot Table
Now, we will utilize cell value as a numerical value to filter a Pivot Table in Excel. To do this, we are going to use the Value Filter option of Excel.
4.1 Finding Values in Pivot Table Based on Exact Value
Numerical values can be used in various ways to filter a Pivot Table. In the first example, we will use the equality criteria to filter our Pivot Table. Let’s say, we want to obtain the name of the Product that has a Price equal to $979. So, let’s follow the instructions mentioned in the following section.
Steps:
- Firstly, use the steps outlined in Step 01 of the first method to create a Pivot Table.
- Then, click on the filter button as shown in the image below.
- Subsequently, choose the Value Filters option.
- After that, select the Equals option.
Consequently, a dialogue box named Value Filter (Product) will appear on your worksheet.
- Next, make sure to choose the Price option as marked in the image below.
- After that, insert the exact value based on which you want to filter the Pivot Table. In this case, we used “979”.
- Lastly, click OK.
As a result, you will have the name of the Product that has a Price of $979 as shown in the following picture.
4.2 Filtering Based on Values That Are Greater Than Specific Value
Now, we will filter the Pivot Table, where we need to find values that are greater than a specified value. Let’s say, we want to show the name of the Products that have Prices greater than $900. So, follow the steps mentioned below to do this.
Steps:
- Firstly, use the steps outlined in Step 01 of the first method to create a Pivot Table.
- Now, click on the filter button as shown in the image below.
- Following that, choose the Value Filters option.
- Then, select the Greater Than option.
Subsequently, a dialogue box named Value Filter (Product) will appear on your worksheet.
- After that, make sure to select the Price option as marked in the image below.
- Then, insert the value based on which you want to filter the Pivot Table. In this case, we used “900”.
- Finally, click OK.
Consequently, you will have the name of the Products that have a Price greater than $900 as demonstrated in the following image.
4.3 Finding Values in Pivot Table That Are Less Than Specific Value
Now, we are going to learn how we can find values in the Pivot Table that are less than a specific value. So, let’s use the steps mentioned in the following section.
Steps:
- Firstly, apply the steps mentioned in Step 01 of the first method to create a Pivot Table.
- After that, click on the filter button as shown in the image below.
- Then, choose the Value Filters option.
- Afterward, select the Less Than option.
As a result, a dialogue box named Value Filter (Product) will open on your worksheet.
- Following that, make sure to select the Price option as marked in the image below.
- Then, enter the value based on which you want to filter the Pivot Table. In this case, we used “600”.
- Lastly, click OK.
Subsequently, you will have the name of the Products that have a Price less than $600 as demonstrated in the following image.
4.4 Filtering Pivot Table Between Two Values
Suppose you want to filter the Pivot Table data between two specific values. For instance, let’s say, you want to find the name of the Products with a Price range from $600 to $1000. So, to do this, follow the instructions outlined below.
Steps:
- Firstly, follow the steps outlined in Step 01 of the first method to create a Pivot Table.
- Following that, click on the filter button as shown in the image below.
- Now, choose the Value Filters option.
- Subsequently, select the Between option.
Subsequently, a dialogue box named Value Filter (Product) will open on your worksheet.
- Then, make sure to select the Price option as marked in the image below.
- Now, enter the two values based on which you want to filter the Pivot Table. In this case, we used “600” and “1000” as the filter range values.
- Finally, click OK.
Consequently, you will have the names of the Products that have a Price between $600 and $1000 as shown in the following picture.
4.5 Finding Top and Bottom 5 Values in Pivot Table
In this section of the article, we will find the top and bottom 5 values from a Pivot Table in Excel. So, let’s follow the guidelines discussed below to do this.
Steps:
- Firstly, follow the instructions explained in Step 01 of the first method to create a Pivot Table.
- Subsequently, click on the filter button as shown in the image below.
- After that, select the Value Filters option.
- Next, choose the Top 10 option.
Consequently, a dialogue box named Value Filter (Product) will open on your worksheet.
- Now, make sure to select the Top option as marked in the image below.
- Afterward, enter 5 as we need the top 5 values.
- Then, choose the Price option as shown in the following picture.
- Lastly, click OK.
Consequently, you will have the top 5 Products with the highest Prices as demonstrated in the following image.
You can also find the bottom 5 values by selecting the Bottom option in the Value Filter (Product) dialogue box, and you will have the following outputs on your worksheet.
5. Employing Slicer to Filter Data Based on Cell Value
Employing the Slicer option is another smart option to filter data based on cell value. The Slicer option allows us to select both single and multiple filtering criteria. Now, let’s use the steps outlined in the following section to do this.
Steps:
- Firstly, follow the steps mentioned in Step 01 of the first method to create a Pivot Table.
- After that, click on any cell of the created Pivot Table to access the PivotTable Analyze tab.
- Then, select the Filter option.
- Following that, choose the Slicer option from the drop-down.
As a result, the Insert Slicers dialogue box will appear on your worksheet.
- Now, check the field of the Product option in the Insert Slicers dialogue box.
- Then, click OK.
Subsequently, a Slicer will be added to your worksheet as shown in the following picture.
- Now, choose any Product from the Slicer to filter the Pivot Table based on your selection. In this case, we selected the “Acer Aspire 3” option.
Consequently, you will have the following outputs as demonstrated in the image below.
Now, follow the instructions given below if you want to apply multiple filtering criteria in Slicer.
- Firstly, click on the Clear Filter option in the Slicer as marked in the following image.
- After that, click on the Multi-Select option in the Slicer.
- Following that, select the name of the Products based on which you want to filter the Pivot Table. Here, we have chosen “Acer Aspire 3”, “Apple MacBook Pro”, and “Asus ZenBook UX430UN” options in the Slicer.
Subsequently, the Pivot Table will be filtered based on your selections as shown in the image below.
6. Inserting Timeline to Filter Data in Pivot Table
Inserting Timeline is another efficient way to filter Pivot Table based on cell value as a date. It is to be noted that the Timeline feature doesn’t work with the texts and numbers. It only works with dates and times. Now, let’s use the instructions given below to insert a Timeline to filter data in Pivot Table based on cell value as a date.
Steps:
- Firstly, follow the steps mentioned in Step 01 of the third method to create a Pivot Table.
- Then, click on any cell of the created Pivot Table to access the PivotTable Analyze tab.
- After that, choose the Insert Timeline option from the Filter group.
Subsequently, the Insert Timelines dialogue box will open on your worksheet.
- Following that, check the field of the Order Date option in the Insert Timelines dialogue box.
- Finally, click OK.
Consequently, a Timeline will be added to your worksheet as shown in the following picture.
- Now, select the range of months based on which you want to filter the Pivot Table. In this case, we selected the months of August, September, and October.
As a result, the Pivot Table will be filtered based on your selected months from the Timeline as demonstrated in the following picture.
Read More: How to Create a Timeline in Excel to Filter Pivot Table
Practice Section
In the Excel Workbook, we have provided a Practice Section in the worksheet. Please practice it yourself.
Download Practice Workbook
Conclusion
So, these were the simple and most effective ways to filter Pivot Table based on cell value in Excel. I strongly advise you to complete the Practice Sections for each example to better grasp the concepts. If you have any questions or suggestions about this article, please leave them in the comments section.
<< Go Back to Pivot Table Filter | Pivot Table in Excel | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
