• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

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

  • Courses
  • Pricing
    • SALE 20% Off All Courses
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Logout
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Filter Excel Pivot Tables by Values

You are here: Home / Excel PivotTables / Filter Excel Pivot Tables by Values
Filter Excel Pivot Tables by Values
September 18, 2013 by Mynda Treacy
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

More Excel PivotTable Posts

show report filter pages for power pivot pivottables

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.

Hide Blanks in Excel PivotTables

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

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

Display Missing Dates in Excel PivotTables

Excel PivotTable Month Comparison [Video]

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 [Video]

Excel PivotTable Add Percentage of Total Column is easy via the Show Values As settings.
Excel Factor 6 Auto Refresh PivotTables

Excel Factor 6 Auto Refresh PivotTables

Excel Factor Entry 1 - Reverse PivotTable

Excel Factor Entry 1 – Reverse PivotTable

More Excel PivotTables Posts

Auto Refresh PivotTables

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

Show Items with no Data in PivotTables allows you to maintain a constant structure to your PivotTable or Pivot Chart axis when filtering.

Force Excel Slicers to Single Select

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

Excel PivotTable Profit and Loss

Creating an Excel PivotTable Profit and Loss Statement means you can use Slicers and Conditional Formatting and have the P&L automatically update.

Excel PivotTable Field List Tips

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

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

Excel Slicer Formatting

Excel Slicer Formatting is essential because they’re big and chunky. In this tutorial I show you how to make Excel Slicers small.
Excel PivotTable Quick Explore

Excel PivotTable Quick Explore

Drill down into data hierarchies using PivotTables and Pivot Charts with Excel PivotTable Quick Explore. New in Excel 2013 onward.
excel online pivottables

Excel Online PivotTables

Excel Online PivotTables are now available from the Insert tab of the ribbon. There are some limitations that are covered in this post.

Excel PivotTable Error Handling

Excel PivotTable error handling and why you can’t calculate the percentage change when the prior period is zero or blank.




Category: Excel PivotTablesTag: Excel PivotTable
Previous Post:Finding Missing Numbers in a Range Using VBAFinding Missing Numbers in a Range Using VBA
Next Post:Excel Find Partial Text StringsExcel Find Partial Text Strings

Reader Interactions

Comments

  1. Karen Marshall

    May 9, 2019 at 3:49 am

    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

      May 9, 2019 at 9:39 am

      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
  2. Gabriel

    April 16, 2019 at 9:40 pm

    Hi Mynda,

    Thank you it worked like a charm! And I will use the forums for future questions.

    Reply
    • Mynda Treacy

      April 16, 2019 at 9:55 pm

      Glad I could help 🙂

      Reply
  3. Gabriel

    April 16, 2019 at 4:58 am

    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

      April 16, 2019 at 9:51 am

      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

    April 13, 2019 at 5:30 am

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

    Thank you :o)

    Reply
    • Mynda Treacy

      April 14, 2019 at 1:37 pm

      🙂 glad you found it helpful, Gabriel.

      Reply
  5. Ozen Kandirali

    November 9, 2014 at 4:14 pm

    I NEED A MAC VERSION OF THIS TRAINING

    Reply
    • Mynda Treacy

      November 9, 2014 at 8:24 pm

      Hi Ozen,

      Sorry, I’m Mac illiterate 🙁

      You could try ExcelJet.com

      Mynda

      Reply
      • Ozen Kandirali

        November 10, 2014 at 1:32 am

        This is great training but it is really hard to find same functions on the mac version. Thank you. I am sure you can be very successful on Mac. You are doing great job.
        Thank you for your answer

        Reply
        • Mynda Treacy

          November 10, 2014 at 8:19 am

          Thanks, Ozen 🙂

          Reply

Leave a Reply Cancel reply

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

Current ye@r *

Leave this field empty

Sidebar

More results...

Course Sale

mynda treacy microsoft mvpHi, I'm Mynda Treacy and I run MOTH with my husband, Phil. Through our blog, webinars, YouTube channel and courses we hope we can help you learn Excel, Power Pivot and DAX, Power Query, Power BI, and Excel Dashboards.

Subscribe to Our Newsletter

Receive weekly tutorials on Excel, Power Query, Power Pivot, Power BI and More.

We respect your email privacy

Guides and Resources

  • Excel Keyboard Shortcuts
  • Excel Functions
  • Excel Formulas
  • Excel Custom Number Formatting
  • ALT Codes
  • Pivot Tables
  • VLOOKUP
  • VBA
  • Excel Userforms
  • Free Downloads

239 Excel Keyboard Shortcuts

Download Free PDF

Free Webinars

Excel Dashboards Webinar

Watch our free webinars and learn to create Interactive Dashboard Reports in Excel or Power BI

Click Here to Watch Now

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
trustpilot excellent rating
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk – For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved

Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.