Forum Discussion
How to filter by sum values pivot table
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
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)
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.
- Thank you, the problem is it will not let me do that on my last column of the pivot which is a value sum I cannot filter on this or sort as you would for any of the other columns
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.
