How to Filter in a Pivot Table?

The PivotTable is an Excel spreadsheet tool that allows us to summarize, group, and perform mathematical operations like SUM, AVERAGE, COUNT, etc., from the organized data stored in a database. Apart from the mathematical operations, the PivotTable has one of the best features: filtering, which allows us to extract defined results from our data.

Let us look at multiple ways of using a filter in an Excel Pivot table: –

#1 – Inbuilt filter in the Excel Pivot Table

  • Let us have the data in one of the worksheets.

The above data consists of 4 columns: Sr.No, Flat No., Carpet Area, and SBA.

  • Go to the “Insert” tab and select a PivotTable, as shown below.

  • The “Create a PivotTable” window pops out when you click on the PivotTable.

In this window, we can select a table or a range to create a PivotTable. Else, we can also use an external data source.

We can also place the PivotTable report in the same worksheet or a new one. For example, we can see this in the above picture.

  • The “PivotTable Fields” is available on the right end of the sheet as below.

  • We can observe the filter field, where we can drag the fields into filters to create a PivotTable filter. Let us drag the “Flat.No” field into “Filters.” We can see the filter for flat no’s would have been created.

  • From this, we can filter the flat numbers as per our requirement, which is the normal way of creating the filter in the PivotTable.

#2 – Create a filter for the Values Area of an Excel Pivot table

Generally, when we take data into value areas, we would not create any filter for those Pivot Table fieldsPivot Table FieldsPivot table calculated fields are formulas with reference to other fields, and calculated values refer to other values within a specific pivot field.read more. However, we can see it below.

We can observe that there is no filter option for value areas: Sum of SBA and Sum of Carpet Area. But we can create it, which helps us in various decision-making purposes.

  • Firstly, we must select any cell next to the table and click on the “Filter” in the “Data” tab.

  • We can see the filter gets in the “Values” area.

As we got the filters, we can perform different types of operations from value areas, like sorting them from largest to smallest to know top sales/area/anything. Similarly, we can sort from smallest to largest, sorting by color, and even perform number filters like <=,<,>=,>, and many more. So, it plays a major role in decision-making in any organization.

#3 – Display a list of multiple items in a Pivot Table Filter.

The above example taught us about creating a filter in the PivotTable.

Now, let us look at how we display the list differently.

The three most important ways of displaying a list of multiple items in a PivotTable filter are:

  • Using Slicers.Creating a list of cells with filter criteria.List of comma-separated-values.

Using Slicers

  • Let us have a simple PivotTable with columns: Region, Month, Unit no, Function, Industry, Age Category.

  • First, create a PivotTable using the above-given data. Then, select the data, go to the “Insert” tab, select a “PivotTable” option, and create a PivotTable.

  • From this example, we will consider the function of our filter. First, let us check how it can be listed using slicers and varies as per our selection. It is simple: we select any cell inside the PivotTable, go to the “Analyze” tab on the ribbon, and choose the “Insert Slicer.”

  • Then, we need to insert the slide in the slicer of the field in our filter area. So, in this case, we must select the “Function” field in our filter area and then press the “OK” button, which will add a slicer to the sheet.

  • We can see items highlighted in the slicer are those highlighted in our PivotTable filter criteria in the filter drop-down menu.

However, suppose you have many items on your list here, which is long. In that case, we might not display those items properly. You might have to scroll to see which items are selected, leading us to the next solution: list the filter criteria in cells.

So, “Create List of Cells With Pivot Table Filter Criteria” comes to our rescue.

Create a List of cells with Pivot Table Filter Criteria: –

We will use a connected PivotTable and the above slicer here to connect two PivotTables.

  • Let us create a duplicate copy of the existing PivotTable and paste it into a blank cell.

So, we have a duplicate copy of our PivotTable, and we will modify it slightly to show the “Function” field in the “Rows” area.

To do this, we have to select any cell inside our PivotTable here, go over to the PivotTable field list, and remove industry from the “Rows.” Also, removing the “Count of Age” category from the “Values” area, we will take the “Function” that is in our “Filters” area to the “Rows” area. 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 items in slicers and function filters.

  • Now, we have a list of our PivotTable filter criteria. It works because both of these PivotTables are connected by the slicer. We can right-click anywhere on the slicer to report connections.

  • PivotTable connections will open up a menu showing that these PivotTables are connected as checkboxes are checked.

It means whenever one change is made in the first PivotTable, it automatically gets reflected in the other.

We can move PivotTables anywhere. For example, we can use it in any financial model and change row labels.

List of Comma Separated Values in Excel Pivot Table Filter: –

So, the third way to display our PivotTable filter criteria is in a single cell with a list of comma-separated values. We can do that with the TEXTJOIN function. We still need the tables we used earlier and just used a formula to create this string of values and separate them with commas.

It is a new formula or function introduced in Excel 2016, called TEXTJOIN(If there is no 2016, you can also use the CONCATENATE function); text joining makes this process much easier.

The TEXTJOIN function provides us with three different arguments:

  • Delimiter – It can be a comma or space.Ignore empty – TRUE or FALSE to ignore blank cells or not.Text – Add or specify a range of cells that contain the values we want to concatenate.

Let us type the TEXTJOIN – (delimiter- which would be “,” in this case, TRUE (as we should ignore empty cells), K: K(like the list of selected items from the filter will be available in this column)to join any value and also ignore any empty value).

  • Now, we see getting a list of all our PivotTable filter criteria joined by a string. So it is a comma-separated list of values.

  • We could hide the cell if we did not want to show these filter criteria in the formula. Select the cell and go to the “Analyze” options tab. Click on “Field Headers,” and that will hide the cell.

So, now we have the list of values in their PivotTable filter criteria. If we change the PivotTable filter, it reflects in all the methods. We can use any one of them. But eventually, for a comma-separated solution, a slicer and the list are required. We can hide them if we do not want to display the tables.

Things to remember about Excel Pivot Table Filter

  • The PivotTable filtering is not an additive because when we select one criterion and want to filter again with other criteria, the first one will get discarded.We got a special feature in the PivotTable filter: “Search Box,” which allows us to manually deselect some of the results we do not want. E.g., If we have a huge list and there are blanks too, then to select blank, we can easily choose by searching for blanks in the search box rather than scrolling down till the end.We cannot exclude certain results with a condition in the PivotTable filter, but we can do it by using the “Label Filter.” E.g., If we want to select any product with a certain currency like rupee or dollar, etc., then we can use a label filter – ‘does not contain’ and should give the condition.

You can download this Excel Pivot table filter template from here – Pivot Table Filter Excel Template.

This article is a guide to the PivotTable Filter in Excel. Here, we discuss how to filter data in a PivotTable with the help of examples and a downloadable Excel template. You may learn more about Excel from the following articles: –

  • Excel Pivot Table From Multiple SheetsExcel Pivot Table From Multiple SheetsPivot Table is a basic data analysis tool that calculates, summarizes, & analyses the data of a more extensive table. To create a Pivot Table from Multiple Sheets, you can use a few shortcuts & features as per the specified conditions. read morePivot Table Count UniqueHow to Delete the Pivot Table?How To Delete The Pivot Table?To delete a pivot table in Excel, you must first select it. Then go to the Analyze menu tab under the Design and Analyze menu tabs and select actions. Then, from the Select option’s drop-down option, select Entire Pivot Table to delete it.read morePivot Chart in ExcelPivot Chart In ExcelIn Excel, a pivot chart is a built-in feature that allows you to summarize selected rows and columns of data in a spreadsheet. It is a visual representation of a pivot table that helps in the summarization and analysis of datasets, patterns, and trends.read more