My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

Filter Excel Pivot Tables by Values

You are here: Home / Excel PivotTables / Filter Excel Pivot Tables by Values
Filter Excel Pivot Tables by Values
by 
This is a guest tutorial written by Bryon Smedley of Bristol, Tennessee.

If you’ve ever used Pivot Tables in Excel, you no doubt have discovered the wonders of filtering.  The ability to filter row or column items can be extremely helpful when you don’t wish to analyze all of the items in the driving data set.

Excel Pivot Table filter by values example

But what do you do if you wish to filter by the Value-based items?  In other words, the numbers in the “connect the dots” area where row and column choices intersect.  These, on first glance, don’t appear to have sorting and filtering controls available.

Pivot Table field list

Rest assured, they do exist; you just have to dig a bit to find them.

If you right-click on any row-based item, the following menu will appear.

value filters

From here, you can filter by any of the traditional controls:

Top 10

  • Filter in either direction. Top or Bottom
  • Filter the target by any Value-based entry (ex: Cost or Sales)
  • Filter by Item count from 1 to the maximum item count in your data set
  • Filter by Percentage from 0% to 100%
  • Filter by Sum from 1 to the maximum sum in your data set

Top 10 filter

Label Filters

Filter the selected column by any text or date criteria (depending on the nature of the data) with the obligatory text-based or date-based filters (i.e. Greater Than, Less Than, Contains, Begins With, etc…)

Label filters

Date filters

Value Filters

Filter the data by the selected Value-based entry (ex: Sales or Cost) with any of the obligatory value-based (i.e. Equal to, Greater Than, Less Than, Is Between, etc…)

Value filters

Bonus Tip

Another nice trick is to select row entries (via the traditional CTRL-Click or Click-Shift-Click selection techniques) and then Right-click on one of the selected items and choose:

  • Keep Only Selected Items
  • Hide Selected Items

Keep Only Selected Items

This allows you to quickly reduce a long list to a short list without the need to check and uncheck a series of tiny boxes.

Thanks

Thanks to Bryon for writing this tutorial. We appreciate you sharing your knowledge.

Bryon is from Bristol, Tennessee and has been teaching Excel since version 7 which was included with Office 95. He is currently a Technical Training Analyst for one of the largest coal companies in the world. His key responsibility is to conduct all Microsoft Office training, but in addition he also serves as a technical consultant for any and all projects involving Microsoft Office applications.
Filter Excel Pivot Tables by Values

Microsoft MVP logo

AUTHOR Mynda Treacy Co-Founder / Owner at My Online Training Hub

CIMA qualified Accountant with over 25 years experience in roles such as Global IT Financial Controller for investment banking firms Barclays Capital and NatWest Markets.

Mynda has been awarded Microsoft MVP status every year since 2014 for her expertise and contributions to educating people about Microsoft Excel.

Mynda teaches several courses here at MOTH including Excel Expert, Excel Dashboards, Power BI, Power Query and Power Pivot.

More Excel PivotTable Posts

show report filter pages for power pivot pivottables
PivotTables created from Power Pivot can't use the 'Show Report Filter Pages' option. But this piece of VBA allows you to do just that.
automating and emailing pivot table reports
Automate the creation and distribution of pivot table reports with some VBA. Send reports to multiple email recipients using Outlook.
Hide blanks in Excel PivotTables caused by empty cells in your source data. I’m talking about PivotTable cells containing the (blank) placeholder.
Conditional Formatting PivotTables
Conditional Formatting PivotTables values areas will automatically expand/contract as you add new data or make changes to the filters, rows or columns.
Display Missing Dates in Excel PivotTables
Excel PivotTable Month Comparison [Video]
Excel PivotTable Month Comparison is easy with the Show Values As tool.
Excel PivotTable Add Percentage of Total Column [Video]
Excel PivotTable Add Percentage of Total Column is easy via the Show Values As settings.
Excel Factor 6 Auto Refresh PivotTables
Excel Factor Entry 1 - Reverse PivotTable

More Excel PivotTables Posts

Sorting Excel Date Slicers
Use these tricks to custom sort Excel Slicer buttons enabling them appear in a specific order that suits your needs.
PivotTable Formatting
Create PivotTables that don’t look like PivotTables with these really easy PivotTable Formatting tips and tricks.
Images in Excel Pivot Tables
Include images in Excel PivotTables, combined with some slick formatting, and create dynamic and visually rich PivotTables like never before.
Auto Refresh PivotTables isn’t on by default, and the process differs depending on if your PivotTables is loaded to the data model or not.
Show Items with no Data in PivotTables allows you to maintain a constant structure to your PivotTable or Pivot Chart axis when filtering.
There's no build in way to force Excel Slicers to single select but we can use these clever warnings to persuade your users.
excel pivottable p&L
Creating an Excel PivotTable Profit and Loss Statement means you can use Slicers and Conditional Formatting and have the P&L automatically update.
Customize the Excel PivotTable Field List to suit your needs. Find how to turn the PivotTable Field List on and off and other handy tips.
Hide blanks in Excel PivotTables caused by empty cells in your source data. I’m talking about PivotTable cells containing the (blank) placeholder.
Excel Slicer Formatting is essential because they’re big and chunky. In this tutorial I show you the tricks to make Excel Slicers small.


Category: Tag:

Reader Interactions

Comments

  1. Karen Marshall

    Hello. I am having a strange problem and hoping for some relief. I am using MS Office Home and Business 2016 on a Windows 7 Pro desktop. I have a few tables in one workbook and have established two separate relationships – one-to-many between tables 1 and 2, one-to-many between tables 1 and 3. I have a good pivot table based on the 1 and 2 tables. When I attempt a pivot table based on tables 1 and 3 and try to filter on values, the drop down list for Value Filter shows the fields from table 2 and not the ones I need from table 3. Am I asking too much of Excel to keep these tables in the same workbook?

    Reply

    • Mynda Treacy

      Hi Karen,

      It sounds like you either don’t have a dimension table containing the field you want to use in your Slicer, or you are using a field in the PivotTable from your Fact table (table 2) instead of your dimension table (table 1).

      If you want to post your question on our Excel forum you can upload your file and we can help further.

      Mynda

      Reply

  3. Gabriel

    Now that I assimilate this awesome info, I have one small issue.

    Context: I’m trying to find how many duplicate entries per category.

    Now with you explanation above, I am now able to see only those duplicate entries in my Pivot Table. However the Subtotal of each category seems to be adding the numbers from other categories.

    I’ve applied 2x things here in my Pivot Table:

    1- In my Values (Count of items in the Categories) \ Value Field Setting \ Show Value As \ I selected: Running Total In \ Base Field: Categories to get a count of each item per category
    2- A value filter of: Does not equal = 1

    Category A has a total of 1x entry showing up twice = total of 2
    Category B has a total of 5x entries showing up duplicated = total of 10

    My issue is about Subtotal for Category B shows 12 instead of 10. Why is that?
    and so on for the other categories adding previous subtotals

    I’m kind of lost on how to have the Subcategory show the totals for only that Category and looking on the web, I can’t any explanation. I’m hoping that you can help me figure this out. MS Excel 2016 is my version.

    I could send you an email or any other way with the example of my file.

    Reply

    • Mynda Treacy

      Hi Gabriel,

      If you’ve added your data to the ‘Data Model’ upon creating your PivotTable then you can right-click the PivotTable > Options > Totals & Filters tab > make sure the box that says ‘Include filtered items in totals’ is unchecked.

      OtherwiseHi Jeff,

      Yes, it was very disappointing that all versions of Excel 2016 didn’t come with the Power tools. We MVPs lobbied Microsoft heavily to have it changed, and thankfully for Excel 2019 it was.

      If you share a file containing a Power Query query or a Power Pivot model then users who do not have these tools will not be able to refresh queries or interact with the Power Pivot model. Refreshing is probably not an issue as you can do this for them. However, if users want to interact with the Power Pivot model e.g. click Slicers, change the structure of a PivotTable i.e. add/remove row/column fields, then they can’t. Basically, users without these tools will use the file as though it’s in view only mode, i.e. they can see it (open the file) but they can’t touch (they can navigate the sheets in the file, but can’t interact with Power Query or Power Pivot tools).

      However, users can open the file in Excel Online and interact with the Power Pivot model once you’ve built it and shared the file with them. Slicers also work in Excel Online.

      If that doesn’t help, please post your question and Excel file on our forum where we can help you further.

      Mynda

      Mynda

      Reply

  4. Gabriel

    This is AWESOME! Soooo many hours of painful manual manipulations saved! Alleluia for this post!

    Thank you :o)

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Current ye@r *