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%3CLINGO-SUB%20id%3D%22lingo-sub-2271621%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20filter%20by%20sum%20values%20pivot%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2271621%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1025116%22%20target%3D%22_blank%22%3E%40colbrawl%3C%2FA%3E%26nbsp%3BTry%20by%20right-clicking%20on%20any%20of%20the%20row%20labels%20of%20your%20pivot%20table.%20It%20should%20open%20a%20window%20where%20you%20can%20select%20%22Filter%22%20and%20then%20%22Value%20Filters...%22.%20Here%20you%20can%20set%20the%20filter%20to%20your%20liking.%20Choose%20%22between%22%20and%20provide%20the%20lower%20and%20upper%20bounds.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Screenshot%202021-04-14%20at%2007.02.20.png%22%20style%3D%22width%3A%20294px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F272698i020E828E68EE5B1D%2Fimage-dimensions%2F294x214%3Fv%3Dv2%22%20width%3D%22294%22%20height%3D%22214%22%20role%3D%22button%22%20title%3D%22Screenshot%202021-04-14%20at%2007.02.20.png%22%20alt%3D%22Screenshot%202021-04-14%20at%2007.02.20.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EPicture%20taken%20on%20a%20Mac%2C%20but%20it's%20similar%20for%20the%20PC%20version.%3C%2FP%3E%3C%2FLINGO-BODY%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

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