# How to filter by sum values pivot table

Copper Contributor

# 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

14 Replies

# Re: How to filter by sum values pivot table

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

# Re: How to filter by sum values pivot table

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

# Re: How to filter by sum values pivot table

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

# Re: How to filter by sum values pivot table

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

# Re: How to filter by sum values pivot table

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

# Re: How to filter by sum values pivot table

It definitely should have been in those Pivot Table tutorials I watched haha. Cheers!

# Re: How to filter by sum values pivot table

Thank you. I only started learning about Pivot Tables this morning and the first thing I wanted to do was this!

# Re: How to filter by sum values pivot table

Amazingly Simple Solution - You are AWESOME!!!! THANK YOU SO MUCH!!!!!

# Re: How to filter by sum values pivot table

@Riny_van_Eekelen thanks, never knew this cool hack :)

# Re: How to filter by sum values pivot table

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

Ha! Nice one!

# Re: How to filter by sum values pivot table

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)

# Re: How to filter by sum values pivot table

An amazing useful tip - Thank you!

# Re: How to filter by sum values pivot table

You are a savior, Thanks alot.