EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Certification Courses
  • 120+ Courses All in One Bundle
  • Login
Home Excel Excel Resources Excel Tools Pivot Table Filter
Secondary Sidebar
Excel Functions
  • Excel Tools
    • Excel Shortcut Redo
    • Reduce Excel File Size
    • Quick Analysis in Excel
    • Goal Seek in Excel
    • Compare Two Lists in Excel
    • Excel Quick Analysis
    • Estimate Template in Excel
    • Pivot Table Count Unique
    • CSV Files into Excel
    • Excel Business Plan Template
    • Excel Export to PDF
    • Free Excel Template
    • Excel Repair
    • Color in Excel
    • Form Controls in Excel
    • Timecard Template in Excel
    • How to Unhide All Sheets in Excel?
    • Power Query in Excel
    • Power View in Excel
    • XML in Excel
    • Excel Evaluate Formula
    • Examples of Excel Macros
    • Consolidation in Excel
    • Ribbon in Excel
    • Excel Conditional Formatting for Dates
    • Protect Sheet in Excel
    • Data Model in Excel
    • Pivot Table Examples
    • Pivot Table Slicer
    • Pivot Table Filter
    • Watch Window in Excel
    • Slicer in Excel
    • Print Gridlines in Excel
    • Convert Numbers to Text in Excel
    • Freeze Columns in Excel
    • Spelling Check in Excel
    • Name Box in Excel
    • Solve Equation in Excel
    • Excel Spreadsheet Examples
    • What If Analysis in Excel
    • How to Print Labels From Excel
    • Excel Named Range
    • Excel TRANSPOSE Formula
    • Excel Merge and Center
    • Excel Freeze Rows
    • HLOOKUP Formula in Excel
    • Excel Create Database
    • Excel Gridlines
    • Excel Spreadsheet Formulas
    • Excel Sort By Number
    • Excel Concatenating Columns
    • Excel AutoCorrect
    • Conditional Formatting For Blank Cells
    • Excel Icon Sets
    • Excel CTRL D
    • Excel Accounting Number Format
    • Excel Regression Analysis
    • Excel Import Data
    • Excel Freeze Panes
    • Excel Calendar
    • Excel Developer Tab
    • Excel Enable Macros
    • Excel Autosave
    • Heat Map in Excel
    • Excel Toolbar
    • Excel Error Bar
    • Excel Status Bar
    • Excel Unprotect Sheet
    • Check mark in Excel
    • Excel Column Filter
    • Excel Header and Footer
    • Excel Drawing
    • Range in Excel
    • Timeline in Excel
    • Excel Lock Formula
    • Excel Table styles
    • Insert New Worksheet in Excel
    • Excel Column Lock
    • Excel Forms for Data Entry
    • QUOTIENT in Excel
    • Excel Sorting
    • Excel Sort by color
    • Excel Data Bars
    • Excel Tool for Data Analysis
    • Excel Flash Fill
    • Excel Auto Fill
    • Excel Quick Access Toolbar
    • Excel Wrap Text
    • Excel Exponential Smoothing
    • Excel ANOVA
    • Excel Merge Two Tables
    • Excel Conditional Formatting in Pivot Table
    • Dynamic Tables in Excel
    • Excel Sort by date
    • Excel Dynamic Range
    • Record Macro in Excel
    • Two Variable Data Table in Excel
    • Merge Cells in Excel
    • One Variable Data Table in Excel
    • Excel Fill Handle
    • CheckBox in Excel
    • Excel Table
    • Excel Combo Box
    • Auto Format in Excel
    • Advanced Filter in Excel
    • Excel AutoFilter
    • Excel Data Filter
    • Excel Data Validation
    • Excel Radio Button
    • Data Table in Excel
    • Text to Columns in Excel
    • Excel List box
    • Excel Solver Tool
    • Scrollbar in Excel
  • Excel Functions (12+)
  • Financial Functions in Excel (17+)
  • Logical Functions in Excel (14+)
  • Lookup and Reference Functions in Excel (35+)
  • Maths Function in Excel (47+)
  • TEXT and String Functions in Excel (26+)
  • Date and Time Function in Excel (24+)
  • Statistical Functions in Excel (59+)
  • Information Functions in Excel (4+)
  • Excel Charts (56+)
  • Excel Tips (222+)
  • Workplace Productivity (4+)
  • Microsoft Office Tools (15+)
  • Excel Formula and Functions (22+)
  • MS Excel Shortcuts (4+)
Excel Functions Courses
  • EXCEL ADVANCED Training
  • EXCEL Training COURSE

Pivot Table Filter

By Karthikeyan SubburamanKarthikeyan Subburaman

Pivot Table Filter

Pivot Table Filter in Excel (Table of Contents)

  • Introduction to Pivot Table Filter
  • How to Filter a Pivot Table in Excel?

Introduction to Pivot Table Filter

A Pivot Table filter is something that we get when we create a pivot table by default. First, create a table using a Pivot Table; we can see the first field, which is either a Row or Column, will have one filter. Click on the drop-down arrow or press the ALT + Down navigation key to go into the filter list. In that drop-down list, we have traditional filter options. In another way, we can filter the data by clicking right on those fields.

Let’s look at the multiple ways of using the filter in PIVOT.

How to Filter a Pivot Table in Excel?

Let us see some of the examples and their explanation to Filter Pivot Table in Excel.

Start Your Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

You can download this Pivot Table Filter here – Pivot Table Filter

Example #1 – Creating Inbuilt Filter in PIVOT Table

Step 1: Let’s have the data in one of the worksheets.

Pivot Table Filter 1.1

The above data consists of 4 different columns with Sl.No, Flat No’s, Carpet Area & SBA.

Step 2: Go to the Insert tab and select the Pivot table as shown below.

Pivot Table Filter 1.2

When you click the pivot table, the “Create Pivot Table” window pops out.

Pivot Table Filter 1.3

We have 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 can place the Pivot table report, whether in the same worksheet or new worksheet, and we can see it as shown in the above image.

Step 3: Pivot table Field will be 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 filter in the Pivot table.

Pivot Table Filter 1.4

Let’s drag the Flat no’s field into Filters, and we can see the filter for Flat no’s would have been created.

All in One Excel VBA Bundle(120+ Courses, 30+ Projects)
Basic ExcelAdvanced ExcelVBA & MacrosExcel Dashboards
Pivot TablesStatistical FunctionsFinancial FunctionsCharts & Graphs
Price
View Courses
120+ Online Courses | 30+ Projects | 500+ Hours | Verifiable Certificates | Lifetime Access
4.9 (64,725 ratings)

Pivot Table Filter 1.5

We can filter the Flat no’s as per our requirement, which is the normal way of creating a filter in the Pivot table.

Example #2 – Creating a Filter to the Value Areas

Generally, when we take data into value areas, there won’t be any filter created to those fields. We can see it as below.

Pivot Table Filter 2.1

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.

Pivot Table Filter 2.2

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

Example #3 – Displaying List of Multiple Items in a Pivot Table Filter

In the above example, we had learned of creating a filter in Pivot. 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
1. Using Slicer

Let’s have a simple pivot table with different columns like Region, Month, Unit no, Function, Industry, Age Category.

Pivot Table Filter 3.1

From this example, we will consider Function in our filter and check how it can be listed using slicers and varies as per our selection.

Pivot Table Filter 3.2

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 and then we’re going to insert the slicer 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.

Pivot Table Filter 3.3

We can see items highlighted in the slicer are highlighted in our filter criteria in the filter drop-down menu. Now, this is a pretty simple solution that does display the filter criteria. We can easily filter out multiple items and 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 the slicer. We can change the function according to our requirement and observe the results vary as per the items selected.

Pivot Table Filter 3.4

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 it leads us to the nest solution of listing out the filter criteria in cells.

So, “Create List of cells with Filter Criteria” comes to our rescue.

2. Create a List of Cells with 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 of a new sheet.

Pivot Table Filter 3.5

So now we have a duplicate copy of our pivot table, and we are going to modify it 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 the 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 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.

Pivot Table Filter 3.6

Now we have a list of our filter criteria, and this works because both of these pivots are connected by the slicer. If we right-click anywhere on the slicer & to report connections – pivot table connections, it will open up a menu that shows us that both of these pivot tables are connected as checkboxes are checked.

PTF 3.7

This means whenever one change is made in 1st pivot, it would automatically get reflected in the other. Tables can be moved anywhere, they can be used in any financial model, and row labels can also be changed.

3. List of Comma Separated Values:

So the third way to display our 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 the 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 you do not have Excel 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 that contain the values we want to concatenate.

PTf 3.8

Let us type text join- (delimiter- which would be “,” in this case, TRUE (as we should ignore empty cells), A: A (as the list of selected items from the filter will be available in this column) to join any value & also ignore any empty value in Pivot Table Filter)

Ptf 3.9

Now we see getting a list of all of our filter criteria joined by a string. So it’s basically a comma-separated list of values, and if we didn’t want to show these filter criteria in the formula, we can just hide the cell.

Ptf 3.9

Just select the cell and go up to analyze options tab; click on field headers & which will hide the cell.

PTF 3.10

So now we have the list of values in their filter criteria. Now, if we make changes in the pivot filter, it reflects in all the methods. We can use any one of there. But eventually, for a comma-separated solution slicer & the list is required. If you don’t want to display the tables, they can be hidden.

Things to Remember

  • Filtering is not an additive because when we select one criterion and if we want to filter again with another criterion, the first one will be discarded.
  • We got a special feature in the filter, i.e. “Search Box”, which allows us to deselect some of the results we don’t want manually. 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 the search box rather than scrolling down till the end.
  • We are not supposed to exclude certain results with the condition in the filter, but we can do this by using “label filter”. For Ex: If we want to select any product with a certain currency like the rupee or dollar, then we can use a label filter – ‘does not contain’ and give the condition.

Recommended Articles

This is a guide to Pivot Table Filter in Excel. Here we discuss How to Create a Pivot Table Filter in Excel along with examples and an excel template. You can also go through our other suggested articles to learn more –

  1. Pivot Table Sort
  2. VBA Pivot Table
  3. VBA Refresh Pivot Table
  4. Pivot Table Formula in Excel
Popular Course in this category
MS Excel Training Bundle
  13 Online Courses |  100+ Hours |  Verifiable Certificates |  Lifetime Validity
4.5
Price

View Course

Related Courses

Excel Advanced Training (16 Courses, 23+ Projects)4.9
Excel Training (23 Courses, 9+ Projects)4.8
2 Shares
Share
Tweet
Share
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Excel Charts
  • Excel Tips
  • All Tutorials
Certification Courses
  • All Courses
  • Excel VBA Course - All in One Bundle
  • VBA Course
  • Excel Data Analysis Course
  • Excel for Marketing Course
  • Excel for Finance Course
  • Excel for HR Training

ISO 10004:2018 & ISO 9001:2015 Certified

© 2022 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA
Free Excel Course

Excel functions, formulas, charts, formatting creating excel dashboard & others

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

Download Pivot Table Filter

EDUCBA

Download Pivot Table Filter

EDUCBA

डाउनलोड Pivot Table Filter

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more