Filters in Pivot tables are not similar like filters in the tables or data we use, in pivot table filters we have two methods to use filters, one is by right click on the pivot table and we will find the filter option for the pivot table filter, another method is by using the filter options provided in the pivot table fields.
How to Filter in a Pivot Table?
The pivot table is a user-friendly spreadsheet tool in excel which allows us to summarize, grouping, perform mathematical operations like SUM, AVERAGE, COUNT, etc. from the organized data that is stored in a database. Apart from the mathematical operations, Pivot table got one of the best feature i.e. filtering, which allows us to extract defined results from our data.
Let’s look at multiple ways of using a filter in an Excel Pivot table : –
#1 – Inbuilt filter in the Excel Pivot Table
- Let’s have the data in one of the worksheet.
The above data consists of 4 different columns with S.No, Flat no’s, Carpet Area & SBA.
- Go to the insert tab and select a Pivot table as shown below
- When you click on the pivot table, “Create a Pivot Table” window pops out
In this window, we have got an option of selecting a table or a range to create a pivot table or we also can use an external data source as well.
We also have an option of placing the Pivot table report whether in same worksheet or new worksheet and we can see this in the above picture.
- Pivot table Field will be available on the right end of the sheet as below
- We can observe the Filters field, where we can drag the fields into filters to create a Pivot table filter. Let’s drag the Flat no’s field into Filters and we can see the filter for Flat no’s would have been created.
- From this we can filter the Flat no’s as per our requirement and this is the normal way of creating the filter in Pivot table.
#2 – Create a filter to Values Area of an Excel Pivot table
Generally, when we take data into value areas there won’t be any filter created to those Pivot Table fields. We can see it as below
We can clearly observe that there is no filter option for value areas i.e. Sum of SBA & Sum of Carpet Area. But we can actually create it and which helps us in various decision-making purposes.
- Firstly, we have to select any cell next to the table and click on the filter in the data tab.
- We can see the filter gets in the value areas.
As we got the filters, we can now perform a different type of operations from value areas as well like sorting them to largest to smallest in order to know top sales/area/anything. Similarly, we can do sorting from smallest to largest, sorting by color and even we can perform number filters like <=,<,>=,> and many more. This plays a major role in decision making in any organization.
#3 – Display list of multiple items in a Pivot Table Filter.
In the above example, we had learned of creating a filter in the Pivot Table. Now let’s look at the way we display the list in different ways.
3 most important ways of displaying a list of multiple items in a pivot table filter are: –
- Using Slicers.
- Creating a list of cells with filter criteria.
- List of Comma Separated Values.
- Let’s have a simple pivot table with different columns like Region, Month, Unit no, Function, Industry, Age Category.
- First, create a pivot table using the above-given data. Select the data then go to insert tab and select a pivot table option and create a pivot table.
- From this example, we are going to consider Function in our filter and let’s check how it can be listed using slicers and varies as per our selection. It is simple as we just select any cell inside the pivot table and we’ll go to analyze tab on the ribbon and choose insert slicer
- Then we’re going to insert the slide the slicer of the filed in our filter area so in this case, the “Function” filed in our filter area and then hit Ok and that’s going to add a slicer to the sheet.
- We can see items are highlighted in the slicer are those which are highlighted in our Pivot Table filter criteria in the filter drop-down menu.
Now, this is a pretty simple solution that does display the filter criteria. By this, we can easily filter out multiple items and can see the result varying in value areas. From the below example it is clear that we had selected the functions that are visible in the slicer and can find out the count of age category for different Industries (which are row labels that we had dragged into the row label field) which are associated with those function that is in a slicer. We can change the function as per our requirement and can observe the results vary as per the items selected.
However, if you have a lot of items in your list here and it’s really long then those items might not be displayed properly and you might have to do a lot of scrolling to see which items are selected so that leads us to the nest solution of listing out the filter criteria in cells.
So, “Create List of cells with Pivot Table Filter Criteria” comes to our rescue.
Create List of cells with Pivot Table Filter Criteria: –
We’re going to use a connected pivot table and we’re basically going to use the above slicer here to connect two pivot tables together.
- Now let us create a duplicate copy of the existing pivot table and paste it in a blank cell.
So now we have a duplicate copy of our pivot table and we are going to modify a little bit to show that Functions field in the rows area.
To do this we have to select any cell inside of our pivot table here and go over to pivot table field list and going to remove Industry from the rows, removing Count of Age Category from the values area and we are going to take the Function that is in our filters area to rows area and so now we can see that we have a list of our filter criteria if we look over here in our filter drop down menu we have the list of item that is there in slicers and function filter as well.
- Now we have a list of our pivot table filter criteria and this works because both of these pivot tables are connected by the slicer. If we right-click anywhere on the slicer & to report connections
- Pivot table connections that will open up a menu which shows us that both of these pivot tables are connected as checkboxes are checked.
Which means whenever one changed is made in 1st pivot it would automatically get reflected in the other.
Tables can be moved anywhere, it can be used in any financial models, row label can also be changed.
List of Comma Separated Values in Excel Pivot Table Filter: –
So the third way to display our pivot table filter criteria is in a single cell with a list of comma separated values and we can do that with the TEXTJOIN function. We still need the tables that we used earlier and just use a formula to create this string of values and separate them with commas.
This is a new formula or new function that was introduced in Excel 2016 & it’s called TEXTJOIN(If there is no 2016 you can use concatenate function as well) text joining makes this process much easier.
TEXTJOIN basically gives us three different arguments
- Delimiter – which can be a comma or space
- Ignore empty – true or false to ignore empty cells or not
- Text – add or specify a range of cells they contain the values we want to concatenate
Let’s type TEXTJOIN – (delimiter- which would be “,” in this case, TRUE (as we should ignore empty cells), K: K(as the list of selected items from the filter will be available in this column)to join any value & also ignore any empty value)
- Now we see getting a list of all of our pivot table filter criteria joined by a string. So it’s basically a comma-separated list of values.
- If we didn’t want to show these filter criteria in the formula we can hide the cell. Just select the cell and go up to analyze options tab, click on field headers & that will hide the cell.
So now we have the list of values in their Pivot Table filter criteria. Now if we make changes in the pivot table filter it reflects in all the methods. We can use any one of there. But eventually for comma separated solution slicer & the list is required. If you don’t want to display the tables they can be hidden
Things to remember about Excel Pivot Table Filter
- Pivot Table Filtering is not an additive because when we select one criterion and if we want to filter again with other criteria then the first one will get discarded.
- We got a special feature in Pivot Table filter i.e. “Search Box” which allows us to deselect manually some of the results that we don’t want. For Ex: If we have got a huge list and there are blanks too then in order to select blank we can easily get selected by searching for blank in search box rather than scrolling down till the end.
- We are not supposed to exclude certain results with a condition in the Pivot Table filter but we can do by using “label filter”. For Ex: If we want to select any product with certain currency like rupee or dollar etc then we can use label filter – ‘doest not contain’ and should give the condition.
You can download this Excel Pivot table filter template from here – Pivot Table Filter Excel Template
This has been a guide to Pivot table filter in Excel. Here we discuss how to Filter Data in Pivot table with the help of examples and downloadable excel template. You may learn more about excel from the following articles –