How to Use a Pivot Table Filter in Excel

Updated on: March 1, 2023

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

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:

Nt K3H6P9ZbyIUA8qjzJle1vcQgGX1UP8TlIwXLPYGJ3JfstPwt T9Fjol6CX4lN5uv9uZt2pXKKB54LUKu86GEH73LAAKB3Kx OO5d33DJktBLnX9afRsbwIKJ8 0eKpflNYVTTp6It1lVMGIxS7gb5n35CbtC9MtHSkNqt5Cf7B6XzcI 4ympd bQ

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.
ulbiAZ1lzI6Px1jalXNzPipn8iHcTb7LRzM5p2G7xq1FY3 wfpQJYD53Ojjj8 1kNp5mPq ILSUdDahiQePQH072FFzrBcIkqviXV7 o51W5xAPrHQldVJ03EDFip1Qiwz8QYZcwNYd8zqR9999UD8eIRcJwOfCVZWf lQj9KjXsc9JcIjy6FVOqiIcS0w

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:DcoesZwTysRYaaZNxkxHbglk 0hySrQPdW2VFomvWb2YDr94fzrfVBu0Ved0tmkrC34NjMlezUtPeLtetWMtyt6R9A8CaajN3YKWw7lpc6ZdgnvdYbBXGf 1pbyG0EjXGOd dEWNULRb2pFBHfyln1kAq0bY EqXzymHDzniUfVrk L8Dbq6GcoBt77hsA

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:

PX1Cethxaw3I4OtpD5ElBCrCMpNy9wb4dLiNWdVfxcHSuVE2QXRuk1mHEs k2p2SCi56vc9XefPcvwoU1C p7oY2QMzFypHVXB6EY3hNojLpH1JeuNa9cmmzQQ8go1j5AEAOD0FIwEori7Rw9Y43h KYvGEL59WooBQXk7S7SlSmlLfcv4CDbHHLfqX96w

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.

QfwJ30bdk dOe7gHGEHv6 2AUxcy6ypZkrASNhMqk2dNh4xM2iEzSLNr8lPh TP JPzg99c2CujAnE0aGaOJZjxlNZVE4YVhiRFEgu8J6OyHNnpoh1i8bicfdxhRwJ55K8vxwA9S1Z7lDOs0E1Mng9l0m7v4PjC7v2sVaZ sfiLQGWvxEPF8vNmf h1B3A

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!

Lorena M. Rodas leverages her experience across film, writing, and production to make complex tech concepts accessible through storytelling. With a background spanning sci-fi, AI, and emerging tech, Lorena translates her depth of knowledge into engaging, educational content. She is an expert at decoding high-level topics to reach broad audiences.
Access 4,242+ video lessons. Instantly.
Start Learning Now →