How to Use a Pivot Table Filter in Excel

Pivot tables are a great aid when summarizing and analyzing data sets. But did you know that most people get intimidated by the worksheet when they need to filter the information? Don’t worry about that anymore! You can beat that giant by learning how to use Pivot Table Filters!

So, keep reading because, at the end of this article, you will feel confident enough to master what scares you. Check these two manners by which you can do so:

Types of Pivot Tables Filters

Before starting, you should first know which filters you have and the differences between each of them. Then, remember to access the settings at the right of your screen board when you click the Pivot Table.

Field Filter:

This seems like the auto filter you can use in basic tables or ranges in Excel. It is related to checking the items you want to see, unchecking those which you want to hide, or specifying the criteria if needed. All of this is to extract the labels or values of a Pivot Table.

To access this function, you need to be clear about what you need to sift and follow the steps below:

1. Click on the filter arrow at the Pivot Table’s header.
2. Uncheck the boxes you don’t need.
3. Click “OK” when you finish.

Notice then the table now will display only the options you have marked and will hide the rest. Moreover, you will find a sifter icon on the right side of the cell you apply the filter. Check the example:

Things change when multiple fields are in the “Rows” section in the Pivot Table’s settings: Excel can show two different groups of items in the same column with just one filter row for both. Relax! You can choose which field you want to apply the filter following these steps:

1. Go to the column's header.
2. Click on the display arrow of the title.
3. At the beginning of the pop-up context window, select the dropdown arrow.
4. Specify the field you want the filter.
5. Mark or unmark the list of items you need. Remember that the list changes according to the field you are in.
6. Click on the “OK” button when finished.

Finally, suppose the Pivot Table is organized by Tabular or Outline form layout, corresponding to different labels sorted in separate columns. In that case, each column header will have its own sifter icon, and you’ll filter them independently.

Filter by Values

Of course, you can extract a concrete item by sifting the value you want. You can access this operation by going above the Pivot Table, just where the headers are, clicking on the filter arrow, and selecting the “Value Filters” option from the contextual menu. Once there, you must choose one of the criteria you have according to your needs:

Report Filter:

This incredible aid allows you to filter an item by a value from another field. That sounds pretty amazing, isn’t it? But first, let’s be clear: To set a Report Filter, you must go to the setting part of your pivot table at the right of your screen board. Notice that within the four areas you have, one is called “Filter.” So the criteria you put into this section will appear at the beginning of the Pivot table. Check the example:

Then you can use the filter according to what you require, and the whole Pivot Table will respond to this primary filter you have above.

Clear Pivot Tables Filters.

On the contrary, when you need to remove a filter, you can go to the column header and click on that option.

However, if you need to delete all filters, you just need to do the following:

1. First, go into the “Data” tab in the ribbon.
2. Next, look for the “Sort & Filter” group tools.
3. Then, click on the “Clear” button.

Alright! Now you’re ready to filter your Pivot Table like a pro. Do you want to know more? Check how you can Filter a Pivot Table with a slicer! And remember that “the more experiments you make, the better” (Ralph Waldo Emerson). Good luck!