How to Filter Excel Pivot Table (8 Effective Ways)

In some cases, we need to filter the Pivot Table for getting our desired output. Luckily, there are many effective methods to do that task. In this article, I’ll discuss the 8 most effective and popular methods to filter Pivot Table in Excel with proper explanation.

As the article will be a little bit lengthy. You may look over the contents to choose the suitable one based on your preference. For your convenience, I’ll cover the easiest example first, then the filtering methods for multiple columns, and lastly a VBA method.


Download Practice Workbook


8 Effective Methods to Filter Excel Pivot Table

This is our dataset where the Product Category is given with their Order Date, Quantity, Sales based on the States of the U.S.

Dataset

You may visit the How to create a Pivot Table article to insert a Pivot Table for the above dataset. Already I created a Pivot Table which is as follows.

Created Pivot Table

Let’s dive into the methods of filtering the Pivot Table.


1. Using Report Filter to Filter Excel Pivot Table

Firstly, we’ll use the Report Filter to screen the information of the Pivot Table.

For example, we want to get the sum of sales for all product categories of Arizona state.

Now, follow the steps below.

⏩ To turn on Report Filter, select the States field and drag down the field into the Filters areas.

Using Report Filter to Filter Excel Pivot Table

⏩ Then, you’ll see a drop-down arrow with the field States.

Using Report Filter to Filter Excel Pivot Table

⏩ If you click on the drop-down arrow, you’ll get all states in the filtering option.

Select the Arizona option and press OK.

Using Report Filter to Filter Excel Pivot Table

⏩ So, the following output is the desired output that you wanted to get.

Using Report Filter to Filter Excel Pivot Table


2. Utilizing Value Filters 

When you need to filter based on value, Value Filters will be a better choice to get such results. More importantly, there are numerous filtering options inside Value Filters. Off them, we’ll see three notable ways of filtering depending on values.

2.1. Value Filters to Get Top Items

For instance, you have to filter the top 5 items on the basis of the sum of sales.

⏩ Click on the drop-down arrow of Row Labels.

⏩ Go to Value Filters > Top 10.

Utilizing Value Filters to Filter Excel Pivot Table

⏩ Input 5 instead of 10 and press OK.

Utilizing Value Filters

This is the expected output.

Utilizing Value Filters


2.2. Value Filters to Return 

Say, you want to filter the top 50% sum of sales from the total sum of sales.

For finding out this, just go on the Top 10 Filter option likewise the previous example.

⏩ And input 50 instead of 10 and choose Percent, and lastly press OK.

Utilizing Value Filters

⏩ So, the following product has the top 50% sales of total sales.

Utilizing Value Filters to Filter Excel Pivot Table


2.3. Value Filters for a Specific Value

Furthermore, you can filter the whole Pivot Table by specifying a value. Suppose, you want to get the sum of sales that is greater than 2500.

⏩ Click on the drop-down arrow of Row Labels.

⏩ Go to Value Filters > Greater Than.

Utilizing Value Filters

⏩ And now, put the specified value in the box that is 2500 and press OK.

Utilizing Value Filters

⏩ Immediately, you’ll get the following output where all the sum of sales is greater than 2500.

Utilizing Value Filters

Read more: How to Filter Unique Values in Excel


3. Applying Label Filters to Filter Excel Pivot Table

While dealing with the Value Filters, you might observe an option i.e Label Filters.

Well, now, we’ll explore its usage.

Assuming that you want to filter the product category that contains Books only.

I mean you want to find the sum of sales for the Books.

⏩ Click on the drop-down arrow of Row Labels.

⏩ Go to Label Filters > Contains.

Applying Label Filters to Filter Excel Pivot Table

⏩ Type the word Books in the desired option of the Label Filter dialog box.

Applying Label Filters to Filter Excel Pivot Table

⏩ After pressing OK, you’ll get the filtered Pivot Table on the basis of Books.

Applying Label Filters to Filter Excel Pivot Table


4. Creating Date Filters

Now, we’ll see another filtering method that is for separating the Pivot Table depending on the dates.

As you already know, we have a field namely Order Date.

And if you want to filter the Pivot Table for a specific date range e.g. 02-15-22 to 5-10-22.

⏩ For doing this, drag the Order Date field inside the Rows areas.

Creating Date Filters

⏩ Then go to Label Filters > Between.

Creating Date Filters

⏩ Fix the desired dates in the following Label Filter dialog box.

Creating Date Filters

⏩ While entering the OK option, you’ll get the following output.

Creating Date Filters

Read more: How to Filter by Date in Excel


Similar Readings


5. Using Search Box to Filter Excel Pivot Table

Using Search Box is really a simple method. Just type the word (e.g. Ohio as shown in the following figure) that you want to filter the Pivot Table.

Using Search Box to Filter Excel Pivot Table

So, the following output is the filtered Pivot Table containing the state Ohio.

Using Search Box to Filter Excel Pivot Table

Read more: How To Search Multiple Items in Excel Filter


6. Using AutoFilter to Screen Pivot Table

If you’re not a new user in Excel, you must have heard the name of the Filter option. Unfortunately, the Filter option is not working for the Pivot Table as shown in the following figure.

Using AutoFilter

But when I keep the cursor over an adjacent cell of the Pivot Table, the Filter option is working.

Using AutoFilter

After clicking on the Filter option, you’ll see surprisingly drop-down arrows for all columns. Now, you can filter any column without following other methods.

Using AutoFilter


7. Method to Filter Multiple Items in Excel Pivot Table

The most sophisticated and popular method of filtering the Pivot Table in Excel is to filter multiple columns. Obviously, it’ll save your time and you can filter on the basis of your requirement quickly.

7.1. Filter Multiple Items Using Slicer

We can filter the Pivot Table on the basis of States in a faster way by using Slicer.

Select a cell within the Pivot Table

⏩ Go to Insert tab > Slicer from the Filters ribbon

⏩ Choose the States while watching the Insert Slicer dialog box.

Filter Multiple Items Using Slicer

⏩ Now, you see a moveable filtering option of States (the right side of the following picture).

Filter Multiple Items Using Slicer

So, you may have a question about how it works?

Just select a state e.g. Arizona from the options as depicted in the following figure. And you’ll get the output instantly at the Pivot Table located at the left side of the figure.

Filter Multiple Items Using Slicer


7.2. Filter Multiple Items Using Comma 

Furthermore, if you want to display the filter criteria in a single cell, you can do that using the TEXTJOIN function.

For example, if you input the following formula in the G4 cell.

=TEXTJOIN(",",TRUE,B6:B15)

Here, “,” is the delimiter, TRUE for ignoring empty cells, B6:B15 is the cell range for the Product Category of the dataset.

Filter Multiple Items Using Comma

After entering the formula, we’ll get the filtering criteria in a single cell whatever we want to filter by selecting in the Slicer.

Filter Multiple Items Using Comma

Here, the filtering criteria i.e. Books, Sports are seen in the G4 cell.


7.3. Filter Multiple Items Using Filter Criteria

In this way of filtering multiple items, I’ll show you how to join two Pivot Tables and apply filtering togetherly for the two tables.

Regarding this, we have to copy the Pivot Table by pressing CTRL + C and paste the table at a close location of the existing table by pressing CTRL + V.

The below image depicts the process.

Filter Multiple Items Using Filter Criteria

After doing that, keep the Row Labels only in the case of the second Pivot Table.

Now, if you choose any state (e.g. Florida) from the Slicer, the filtering method will work for two Pivot Tables simultaneously.

The first Pivot Table filters product categories whereas the second table filters on the basis of States.

Filter Multiple Items Using Filter Criteria

So what’s the fact behind this filtering?

Right-click on the Slicer, and select the Report Connections option.

Filter Multiple Items Using Filter Criteria

You’ll get the following figure. The figure demonstrates that PivotTable19 (the first table) & PivotTable21(the second table) are joined.

Filter Multiple Items Using Filter Criteria

Read more: Filter Multiple Criteria in Excel


8. Excel Pivot Table Filter Based on a Cell Value Using VBA 

Lastly, I’m going to discuss a case, if you want to filter the Pivot Table for a specific cell value.

Let’s examine that you want to filter the whole Pivot Table on the basis of the state of Florida (located at the E7 cell).

Excel Pivot Table Filter Based on a Cell Value Using VBA

Now follow the steps below.

Step 1:

As we want to use VBA, we must know how to insert a VBA code.

Firstly, open a module by clicking Developer > Visual Basic.

How to Insert VBA Code

Secondly, go to Insert>Module.

How to Insert VBA Code

Step 2:

Now copy the following code into your module.

Sub Filter_UsingVBA()
   Dim pvFld As PivotField
   Dim strFilter As String
   Set pvFld = ActiveSheet.PivotTables("PivotTable23").PivotFields("States")
   strFilter = ActiveWorkbook.Sheets("Sheet14").Range("E7").Value
   pvFld.CurrentPage = strFilter
End Sub

Excel Pivot Table Filter Based on a Cell Value Using VBA

In the above code, I declared pvFld as PivotField and strFilter as String type. Then the required source for pvFld and strFilter are fixed.

Three basic things in the code are-

  • Pivot Table Name: PivotTable23
  • Field Name of the Table: States
  • Active Sheet Name: Sheet14
  • Location of Filter Value: E7 cell

Step 3:

Next, run the code (the keyboard shortcut is F5 or Fn+F5). And you’ll get the filtered output.

Excel Pivot Table Filter Based on a Cell Value Using VBA

Read more: Excel Filter Data Based on Cell Value


Conclusion

In the above article, I tried to cover the methods for filtering the Pivot Table in Excel. I strongly believe that this article will articulate your Excel journey. However, if you have any queries or suggestions, please let me know in the comments section below.


Further Readings

Md. Abdul Kader

Md. Abdul Kader

Hi! I am Abdul Kader and presently working as ‘Excel & VBA Content Developer’ at Exceldemy. I publish my articles related to Microsoft Excel here. In 2019, I completed my graduation in Urban and Regional Planning from Chittagong University of Engineering and Technology. Having extreme eagerness to learn, I want to develop my skills and capabilities higher.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo