How to Filter Values in Pivot Table in Excel [4 Methods]

In the Pivot Table, you will need to filter items based on the sales amount, category, quantity, and so on. Fortunately, Excel has multiple features to filter values in pivot tables in Excel. In this article, you will find 4 methods to filter values in Pivot Tables in Excel. Here you will also find different types of filters in a pivot table. So, let’s begin the journey of learning to filter value in Pivot Tables in Excel.

Add Filter in Pivot Table in Excel

I have a pivot table of a sales report. Here, I have the quantity and sales of each category.

Created PivotTable to filter values in Pivot Table in Excel

I want to add the salesman in the Filters section in the PivotTable Fields. So, I can optimize the performance of each salesman.

  1. Firstly, put the cursor on the salesman field.
  2. Next, drag it to the Filters section.
    Afterward, you will see the added filter in the pivot table.
    Added Filters to filer values in Pivot Table in Excel

Types of Filters in Pivot Table in Excel

Excel offers different types of filters in a pivot table. You can choose your filter type, such as label filters, values filters, row labels, column labels, etc.

1. Label Filters

Label Filters help you to filter text-based data in the pivot table. For example, if you want to apply conditions to some specific items, you better use label filters.

To access the Label Filters, click on the dropdown of the row labels. Now, choose Label Filters.

Clicked dropdown of row labels to access Label filters in Excel

Afterward, you will see the following options in Excel. Now, you can choose filters as per your needs.
List of Label Filters to filer values in Pivot Table in Excel

2. Value Filters

Value Filters filter the values, and show or hide values according to applied conditions in the pivot table. For instance, I can filter a specific sales data column to show only the sales of more than $400. As a result, it will show sales greater than $400 and hide the rest of the data.

To get the option of Value Filters, hit the dropdown of the row labels. Now, select Value Filters.

Clicked on the dropdown of Row Labels to access Value Filters in Excel

If you go to this option, you will see the selection lists.

Lists of Value Filters to filter values in Pivot Table in Excel

3. Row Labels Filter

Row Labels filter the data of rows in a pivot table. To access these row labels, you can just tap on the dropdown of row labels. See the image, the category option is for filtering the rows.

Row Labels filter to filter rows in Pivot Table in Excel

Now, you can choose the row options. Here category is the row label. For example, if I want to see the grocery and electronics details, I will check on the options and uncheck others.

After clicking on the dropdown, list of row labels in Pivot Table

4. Column Labels Filter

Column Labels filter the data of columns in a pivot table. To access these row labels, you can just tap on the dropdown of row labels. See the image, the category option is for filtering the columns.

Column Labels to filter column values in Pivot Table in Excel

Now, you can choose the column options. Here, the salesman is the column label.  For example, if I want to see the two employee details, I will check on the names and uncheck others.

After clicking the dropdown of Column Labels, the list of column values

5. Report Filters

Report Filters are on the top of the pivot table. You can place one or more items from the list in this filter. To access this, drag the field name to the Filters section.

Report filters area in PivotTable Fields in Excel

Now, choose the options. Here, the salesman is a report filter.  For example, if I want to see the sale details of a salesman Harould. So, I check on that name and uncheck others.

Clicking on dropdown of Report Filters and the list of filter values in Pivot Table

6. Slicer Filters

In a pivot table, you can choose slicer filters from the list. To do that:

  1. Click a cell on the pivot table.
  2. Then, go to the PivotTable Analyze tab.
  3. Next, click on Insert Slicer from the Filter group.
  4. Now, choose the field from the list of Insert Slicers.
    Steps to Insert Slicers and the list of Slicers in Pivot Table

Here, I have chosen Salesman. So, it will show the list of the salesman. You can filter data from this list.
The list of selected slicer to filter values in Pivot table

7. Timeline Filters

In a pivot table, if you have date, month, year, and time, you can choose timeline filters from the list.

  1. Click a cell on the pivot table.
  2. Then, go to the PivotTable Analyze tab.
  3. Next, click on Insert Timeline from the Filter group.
  4. Now, choose the field from the list of Insert Timelines.
    Steps to insert timeline to filter period in Pivot Table in Excel

Now, you will see the timeline of the date. You can filter the data based on period.
Inserted Timeline to filter values based on period in Pivot Table in Excel

Filter Data Using Label Filters in Pivot Table in Excel

I have a sales report here. I have a list of product names with categories. Here, we have the sum of quantity with sales values.
Pivot Table data to filter Label Filters and Value Filters in Excel

Now, I want to filter the list with only electronics products. Follow these steps:

  1. Firstly, click on the dropdown of row labels.
  2. Then, select Label Filters.
  3. Next, click on the equals option.
    Accessed the Label Filter to filter values in Pivot Table in Excel
  4. Now, insert the desired category name in the Label Filter dialog box and click OK.
    Inserted the label name in Label Filter to filter in Pivot Table in Excel

The result is here. We have filtered the data of product names in the electronics category.
Product list of electronics category using Label Filters to filter values in Excel

Filter Data by Value Filters in Pivot Table in Excel

If you want to filter the data according to value, you can use Value Filters in the pivot table in Excel. I want to filter products that have sales greater than $15000. Now, follow the guides below:

  1. At first, click on the dropdown of row labels.
  2. Then, select Value Filters.
  3. Next, choose the Greater than option from the list.
    Accessed Value Filters with list of filter options in Pivot Table in Excel
  4. Choose the items first and then, insert the number with the unit to filter the data in the Value Filter dialog box.
  5. Then, click OK.
    Inserted values in the Value Filter in Pivot Table in Excel
    You have filtered data of the sum of sales which are greater than $15000. The rest data will be hidden.
    Product list after filtering with Value Filters in Pivot Table

Filter by Row in Pivot Table in Excel

I want to filter the sales report based on category and salesman. In this pivot table, the row label is the category.

Pivot Table data to filter by row and by column in Pivot Table in Excel

To filter by row in Pivot Table in Excel:

  1. Firstly, click on the dropdown of the row label.
  2. Next, check on the options you want to choose. Then, uncheck others.
  3. Click OK.
    Choose list of products to filter values in rows in Excel

You will see the filtered result now. Here, we got data for electronics, sanitation, and sports data.
Filtered rows in Pivot Table after applying filters to the Category

Filter by Column in Pivot Table in Excel

In this dataset, we will filter data by column. Here the column label is salesman. I want to filter data for Harould and Kevin.

To filter the column value, go through the process below:

  1. Initially, hit the dropdown of the column labels.
  2. Then, choose the desired name from the lists.
  3. Now, click OK.
    Accessed column labels to filter column values in Pivot Table

Here is the filtered result. Hence, we got the sales data for Harould and Kevin.
Filtered Column after applying filter in columns in Pivot Table in Excel

Clear Filters in Pivot Table in Excel

You will need to show the hidden data after filtering a pivot table in Excel. So, you have to clear all filters in Excel. In the following dataset, there are row labels and column label filters. Now, we will learn to clear them.

Pivot Table data to clear filters from Pivot Table in Excel

Case 1: Clear Filters Individually

Sometimes, you have to clear filters of certain fields. For example, I want to remove the filter of the row labels here.

  1. Hit the dropdown for the row labels.
  2. Select the clear filter from the category options.
    Selected Clear Filter From "Category" to clear filter individually in PivotTable
    After that, you will see, you have cleared the row labels. However, there are still column label filters.
    Cleared filtered from rows in Pivot Table in Excel

Case 2: Using Clear Filters Button

If you want to remove all filters at once, go through these steps:

  1. First, click any cell on the pivot table.
  2. Then, go to the PivotTable Analyze tab.
  3. Next, hit the dropdown of Clear from Actions tools.
  4. Now, select Clear Filters.
    To clear all filters at once, selected Clear Filters from Pivot Table in Excel
    Now, you have cleared all filters just in one click.
    Cleared all filters at once from Pivot Table in Excel

Conclusion

To sum up, you will face situations where you have to filter data in a pivot table. Basically, you have to optimize data or sales based on employees, date, timeline, category, and so on. In this article, I have discussed how to add filters in a pivot table in Excel. Moreover, I have described the types of filters in the pivot table in Excel. I have also included 4 ways to filter values pivot tables in Excel. If you want to remove the filter, I have also added guides describing two different cases in Excel. So, you will get a detailed guideline to filter values in pivot tables in Excel.

Frequently Asked Questions

How to filter top 10 values in pivot table?

To filter the top 10 values in a PivotTable in Excel:

  1. Select the field you want to filter within the PivotTable.
  2. Click the filter drop-down arrow next to the field name.
  3. In the filter options, choose Value Filters.
  4. Select Top 10 from the list.
  5. Specify whether you want to show the top or bottom values in the Top 10 Filter dialog box.
  6. Enter the number of items you want to display in the Top 10 Filter dialog box

The Pivot Table will filter and display the top 10 values based on the selected field. Filtering for the top 10 values allows you to focus on the most significant data points in your Pivot Table.

How to filter out zero values in the pivot table?

To filter out zero values in a PivotTable in Excel:

  1. Select the field you want to filter within the Pivot Table.
  2. Click the filter drop-down arrow next to the field name.
  3. In the filter options, choose Value Filters.
  4. Select Does Not Equal from the list.
  5. In the Value Filter dialog, enter 0 as the value.

The Pivot Table will filter and display data, excluding zero values based on your criteria.
Filtering out zero values allows you to focus on data points that are not equal to zero, helping you analyze non-zero data more effectively.

How to filter pivot table values from largest to smallest?

To filter a Pivot Table from largest to smallest values in Excel:

  1. Click the drop-down arrow in the column header of the field you want to sort.
  2. In the Filter options, select Sort Largest to Smallest or Z to A if the data is text.

The PivotTable will be sorted in descending order, displaying the largest values first. This sorting option allows you to arrange your PivotTable data from the highest values to the lowest, making it easier to identify top performers or prioritize data.

How to filter unique values in pivot table?

To filter unique values in a PivotTable in Excel:

  1. Select the field you want to filter within the PivotTable.
  2. Click the filter drop-down arrow next to the field name.
  3. In the filter options, choose Value Filters.
  4. Select Equals from the list.
  5. In the Value Filters dialog box, enter the desired unique value from the field.

The PivotTable will filter and display only the unique values based on your criteria. Filtering for unique values allows you to focus on specific data points that occur only once within the field. This can help you identify distinct or uncommon values in your PivotTable data.

How to filter sum values in pivot table?

To filter sum values in a PivotTable in Excel:

  1. Select the field with sum values you want to filter within the Pivot Table.
  2. Click the filter drop-down arrow next to the field name.
  3. In the filter options, choose Value Filters.
  4. Select a filter criterion, such as Greater Than or Less Than, based on your sum values.
  5. In the Value Filters dialog box, enter the desired sum value.

The Pivot Table will filter and display data that meets the specified sum value criteria. Filtering for sum values allows you to focus on data points that meet specific aggregation criteria, such as values greater or less than a certain sum. This can be useful for analyzing data with specific sum thresholds.

5/5 - (2 votes)

Similar Posts

Leave a Reply

Your email address will not be published. Required fields are marked *