Apr 13 2021 01:10 PM
Hi- I am trying to figure out how to filter within my sum values final column of my pivot table. I need to look at values higher than 500 and less than 1000 only within the values and I cannot filter on this column to enable looking between the values
Apr 13 2021 10:07 PM
@colbrawl Try by right-clicking on any of the row labels of your pivot table. It should open a window where you can select "Filter" and then "Value Filters...". Here you can set the filter to your liking. Choose "between" and provide the lower and upper bounds.
Picture taken on a Mac, but it's similar for the PC version.
Apr 14 2021 03:24 AM
Apr 14 2021 03:36 AM
@colbrawl Try this then. Some sort of hack that's not officially documented as an Excel feature I believe.
Select the cell directly to the right of the Grand Total column.
and put a filter on via the Home ribbon.
Now you will have a filter icon on every column in the pivot table.
Apr 20 2021 08:29 AM
Apr 20 2021 08:32 AM
@Zalejo Great! Discovered it by chance, several years ago and use it every now and then.
Apr 20 2021 01:34 PM
Aug 13 2021 12:56 AM
May 06 2022 09:03 AM
Jan 24 2023 05:53 PM
@Riny_van_Eekelen thanks, never knew this cool hack
Feb 02 2023 06:46 AM
Feb 22 2023 01:59 PM
The problem with this suggested method is that it only filters for the pivot table rows that are already expanded. If you expand collapsed rows after applying the filter you may still see unwanted rows. I am not sure how this effects totals.
In my use case, below was a more proper solution. In this case, I filtered for all "Bed IDs" where Sum of Blocked Time was greater than 1440 (minutes)