How to Filter Multiple Values in Excel Pivot Table?

Sharing is caring!

If you work with large datasets in Microsoft Excel, you’re likely familiar with pivot tables – a powerful tool for summarizing, analyzing, and presenting data. One common task when working with pivot tables is filtering the data to focus on specific subsets. In this article, we’ll show you step-by-step how to filter multiple values in an Excel pivot table, allowing you to efficiently narrow down your data and gain valuable insights.

Filtering a Single Value in a Pivot Table

Before we explore how to filter multiple values, let’s start with the basics – filtering a single value in your pivot table.

Step 1: Create Your Pivot Table

Begin by creating a pivot table from your dataset. To do this:

1. Select any cell within your data range.
2. Go to the Insert tab on the Excel ribbon.
3. Click on PivotTable in the Tables group.
4. Choose your data range and click OK.

Step 2: Add Fields to Your Pivot Table

Once your pivot table is created, add the relevant fields to the Rows, Columns, Values, and Filters areas. Simply drag and drop the fields from the PivotTable Fields list into the appropriate areas.

Step 3: Apply a Single Filter

To filter a single value:

1. Click on the dropdown arrow next to the field you want to filter.
2. Uncheck the (Select All) option to deselect all values.
3. Check the box next to the specific value you want to include.
4. Click OK to apply the filter.

Your pivot table will now display only the data corresponding to the selected filter value.

Filtering Multiple Values in a Pivot Table

Now that you know how to filter a single value, let’s move on to the main topic – filtering multiple values in your pivot table.

Method 1: Using the Checkbox Method

The checkbox method is the most straightforward way to filter multiple values. Here’s how it works:

Step 1: Access the Filter Options

Click on the dropdown arrow next to the field you want to filter. This will open the filter options window.

Step 2: Select Multiple Values

In the filter options window:

1. Uncheck the (Select All) option to deselect all values.
2. Check the boxes next to the specific values you want to include in your filter. You can select as many values as needed.
3. Click OK to apply the filter.

Your pivot table will now display data for only the selected filter values.

Method 2: Using the Search Box

If your pivot table contains a large number of values, scrolling through the checkbox list can be tedious. In such cases, using the search box can be a quicker way to find and select the values you want to filter.

Step 1: Access the Filter Options

Click on the dropdown arrow next to the field you want to filter. This will open the filter options window.

Step 2: Use the Search Box

In the filter options window:

1. Click on the Search box at the top of the value list.
2. Type in the value you want to filter. The list will automatically update to show values that match your search term.
3. Check the boxes next to the values you want to include in your filter. You can select multiple values by repeating the search process for different terms.
4. Click OK to apply the filter.

Your pivot table will now display data for only the selected filter values.

Method 3: Using Wildcard Characters

Wildcard characters are special symbols that represent one or more characters. They can be useful when you want to filter values based on a pattern rather than specific values.

The two wildcard characters used in Excel are:

• Asterisk (*): Represents any number of characters.
• Question mark (?): Represents a single character.

Here’s how to use wildcard characters for filtering:

Step 1: Access the Filter Options

Click on the dropdown arrow next to the field you want to filter. This will open the filter options window.

Step 2: Use Wildcard Characters

In the filter options window:

1. Click on the Search box at the top of the value list.
2. Type in your wildcard pattern. For example:

• “A*” will filter values starting with “A”.
• “?pple” will filter values that have any single character before “pple”, such as “Apple” or “apple”.

3. Check the boxes next to the values you want to include in your filter. You can select multiple values by repeating the search process with different wildcard patterns.

4. Click OK to apply the filter.

Your pivot table will now display data for only the values matching your wildcard filter.

Advanced Filtering Techniques

In addition to the basic methods described above, there are a few advanced techniques you can use to filter your pivot table data more efficiently.

Using Slicers

Slicers are graphical filter controls that allow you to quickly filter your pivot table by clicking on buttons. To add a slicer:

1. Click anywhere in your pivot table.
2. Go to the PivotTable Analyze tab on the Excel ribbon (or Analyze tab in Excel 2016 and earlier).
3. Click on Insert Slicer in the Filter group.
4. Select the field(s) you want to filter and click OK.

Slicers will appear next to your pivot table. You can filter multiple values by clicking on the desired buttons in the slicer.

Using the Report Filter

The report filter is a special filter area in your pivot table that allows you to filter the entire table by one or more fields. To use the report filter:

1. Drag the field you want to use as a filter to the Filters area in the PivotTable Fields list.
2. Click on the dropdown arrow next to the field in the Filters area.
3. Select the values you want to include in your filter.

The report filter will apply to the entire pivot table, allowing you to quickly switch between different views of your data.

Filtering Pivot Tables with VBA

If you frequently need to filter your pivot table based on specific criteria, you can automate the process using VBA (Visual Basic for Applications). Here’s a simple example of how to filter multiple values in a pivot table using VBA:

Sub FilterPivotTable()
    Dim pt As PivotTable
    Dim pf As PivotField

    Set pt = ActiveSheet.PivotTables("PivotTable1")
    Set pf = pt.PivotFields("Category")

    pf.ClearAllFilters
    pf.CurrentPage = "(All)"

    With pf
        .PivotItems("Category1").Visible = True
        .PivotItems("Category2").Visible = True
        .PivotItems("Category3").Visible = True
    End With
End Sub

This VBA macro filters the “Category” field in “PivotTable1” to display only “Category1”, “Category2”, and “Category3”. You can modify the macro to fit your specific needs.

Best Practices for Filtering Pivot Tables

To make the most of pivot table filters, keep these best practices in mind:

1. Use descriptive names for your fields: Clear and concise field names make it easier to understand and apply filters.
2. Filter before adding fields: Apply filters to your data before adding fields to your pivot table. This will make your pivot table more manageable and improve performance.
3. Use wildcards judiciously: While wildcards can be powerful, overusing them can lead to unintended results. Be specific in your wildcard patterns to avoid accidentally including unwanted data.
4. Combine filters for complex criteria: You can apply multiple filters to different fields to create complex filtering criteria. This allows you to drill down into your data with great precision.
5. Experiment with different filter combinations: Try out various filter combinations to uncover new insights and patterns in your data. Pivot tables are designed for exploratory data analysis, so don’t be afraid to experiment.

Final Thoughts

Filtering multiple values in an Excel pivot table is a crucial skill for anyone working with large datasets. By mastering the techniques outlined in this article – from basic checkbox filtering to advanced methods like slicers and VBA – you’ll be able to efficiently analyze and present your data, uncovering valuable insights that might otherwise remain hidden.

Remember, the key to successful data analysis is to start with a clear question, apply appropriate filters, and iterate as needed. With practice and experimentation, you’ll become a pivot table filtering pro in no time!

FAQs

Vaishvi Desai

Vaishvi Desai is the founder of Excelsamurai and a passionate Excel enthusiast with years of experience in data analysis and spreadsheet management. With a mission to help others harness the power of Excel, Vaishvi shares her expertise through concise, easy-to-follow tutorials on shortcuts, formulas, Pivot Tables, and VBA.