How to filter by sum values pivot table

New 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

8 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!!!!!