How to Filter Excel Pivot Table (8 Effective Ways)

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

We created a Pivot Table which is as follows. Let’s dive into the methods of filtering it.

Method 1 – Using Report Filter to Filter an Excel Pivot Table

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

• You’ll see a drop-down arrow with the field States.

• Click on the drop-down arrow and you’ll get all states in the filtering option.
• Select Arizona and press OK.

• The table has been filtered to use information related to Arizona only.

Method 2 – Utilizing Value Filters

Case 2.1 – Value Filters to Get Top Items

Let’s filter the top 5 items based on total sales.

• Click on the drop-down arrow of Row Labels.
• Go to Value Filters and choose Top 10.

• Input 5 instead of 10 and press OK.

• This is the expected output.

Case 2.2 – Value Filters to Return 

Let’s filter the top 50% sum of sales from the total sum of sales.

• Go to the Top 10 filter (see the previous case).
• And input 50 instead of 10, choose Percent in the box next to it, and press OK.

• The following products are in the top 50% sales of total sales.

Case 2.3 – Value Filters for a Specific Value

Suppose you want to get the sum of sales that is greater than 2,500.

• Click on the drop-down arrow of Row Labels.
• Go to Value Filters and choose Greater Than.

• Put the specified value in the box (2500) and press OK.

• You’ll get the following output where all the sum of sales is greater than 2,500.

Method 3 – Applying Label Filters to Filter an Excel Pivot Table

Let’s filter the product category that contains Books only and find the sum of sales for Books.

• Click on the drop-down arrow for Row Labels.
• Go to Label Filters and pick Contains.

• Type the word Books in the textbox of the Label Filter dialog box.

• Press OK, and you’ll get the filtered Pivot Table on the basis of Books.

Method 4 – Creating Date Filters

Let’s filter the Pivot Table for a specific date range e.g. 02-15-22 to 5-10-22.

• Drag the Order Date field inside the Rows areas when creating the pivot table.

• Go to Label Filters and select Between.

• Input the desired dates in the Label Filter dialog box.

• Press OK, and you’ll get the following output.

Method 5. Using the Search Box to Filter an Excel Pivot Table

You can type the word (e.g. Ohio as shown in the following figure) that you want to use to filter the Pivot Table.

Here’s the filtered Pivot Table for Ohio.

Method 6 – Using AutoFilter to Screen a Pivot Table

Here’s a conundrum. The Filter option is not working for the Pivot Table as shown in the following figure.

But when we keep the cursor over a cell adjacent to the Pivot Table, the Filter option is working. We can pick it from there.

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

Method 7 – Filter Multiple Items in an Excel Pivot Table

Case 7.1 – Filter Multiple Items Using a Slicer

• Select a cell within the Pivot Table.
• Go to Insert tab and choose Slicer from the Filters ribbon.
• Choose States in the Insert Slicer dialog box.

• You will see a moveable filtering option of States (the right side of the following picture).

• Select a state (e.g. Arizona) from the options.
• You’ll get the filtered table based on the state you chose.

Case 7.2 – Filter Multiple Items Using Commas

• Input the following formula in the G4 cell.

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, and can use it in the Slicer.

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

Case 7.3 – Filter Multiple Items Using Filter Criteria

• Copy the Pivot Table by pressing Ctrl + C.
• Paste the table close to the existing table by pressing Ctrl + V.
• Here’s how you can create the setup.

• Keep the Row Labels only in the second Pivot Table.
• If you choose any state (e.g. Florida) from the Slicer, the filtering method will work for both Pivot Tables simultaneously.
• The first Pivot Table filters product categories while the second table filters based on States.

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

Method 8 – Excel Pivot Table Filtering Based on a Cell Value Using VBA 

Let’s filter the whole Pivot Table to display results for Florida (located at the E7 cell).

Steps:

• Open a module by clicking Developer and selecting Visual Basic.

• Go to Insert and pick Module.

• 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

• Run the code (the keyboard shortcut is F5 or Fn + F5). You’ll get the filtered output.

Download Practice Workbook

How to Filter Excel Pivot Table: Knowledge Hub

<< Go Back to Pivot Table in Excel | Learn Excel