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.
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.
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.
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
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…)
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…)
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
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.
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?
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
Hi Mynda,
Thank you it worked like a charm! And I will use the forums for future questions.
Glad I could help 🙂
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.
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
This is AWESOME! Soooo many hours of painful manual manipulations saved! Alleluia for this post!
Thank you :o)
🙂 glad you found it helpful, Gabriel.
I NEED A MAC VERSION OF THIS TRAINING
Hi Ozen,
Sorry, I’m Mac illiterate 🙁
You could try ExcelJet.com
Mynda
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
Thanks, Ozen 🙂