How to filter by sum values pivot table

Copper Contributor

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

14 Replies

@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.

 

Screenshot 2021-04-14 at 07.02.20.png

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.

Screenshot 2021-04-14 at 12.30.29.png

and put a filter on via the Home ribbon.

Screenshot 2021-04-14 at 12.33.54.png

Now you will have a filter icon on every column in the pivot table.

Screenshot 2021-04-14 at 12.30.44.png

 

THANK YOU SO MUCH!!! I've been breaking my head to make it work!! Amazing hack to be honest.

@Zalejo Great! Discovered it by chance, several years ago and use it every now and then.

It definitely should have been in those Pivot Table tutorials I watched haha. Cheers!
Thank you. I only started learning about Pivot Tables this morning and the first thing I wanted to do was this!
Amazingly Simple Solution - You are AWESOME!!!! THANK YOU SO MUCH!!!!!

@Riny_van_Eekelen thanks, never knew this cool hack :)

huge help with this one... thanks for the trick!
Ha! Nice one!

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)

 

MattRoor_0-1677102907219.png

 

An amazing useful tip - Thank you!
You are a savior, Thanks alot.