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.

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.

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.

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

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

Select the **Arizona **option and press **OK**.

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

### 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**.

⏩ Input **5 **instead of **10 **and press **OK**.

This is the expected output.

#### 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**.

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

#### 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**.

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

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

**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**.

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

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

### 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.

⏩ Then go to **Label Filters** > **Between**.

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

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

**Read more:** **How to Filter by Date in Excel**

**Similar Readings**

**How to Filter Multiple Columns Simultaneously in Excel (3 Ways)****Filter Multiple Rows in Excel (11 Suitable Approaches)****How to Filter Horizontal Data in Excel (3 Methods)****Shortcut for Excel Filter (3 Quick Uses with Examples)**

### 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**.

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

**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.

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

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.

### 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.

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

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.

#### 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.

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

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.

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**.

So what’s the fact behind this filtering?

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

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

**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).

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**.

Secondly, go to **Insert**>**Module**.

**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
```

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.

**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.