How to filter by sum values pivot table

%3CLINGO-SUB%20id%3D%22lingo-sub-2270914%22%20slang%3D%22en-US%22%3EHow%20to%20filter%20by%20sum%20values%20pivot%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2270914%22%20slang%3D%22en-US%22%3E%3CP%3EHi-%20I%20am%20trying%20to%20figure%20out%20how%20to%20filter%20within%20my%20sum%20values%20final%20column%20of%20my%20pivot%20table.%20I%20need%20to%20look%20at%20values%20higher%20than%20500%20and%20less%20than%201000%20only%20within%20the%20values%20and%20I%20cannot%20filter%20on%20this%20column%20to%20enable%20looking%20between%20the%20values%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2270914%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
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

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