Pivot tables: an interesting find

[moonfish]

For a while now, I've been trying to find a way of viewing certain entries of a pivot table without changing the calculations.

If I put a number filter or value filter on row labels, the calculation totals and averages are changed and it's not possible to put a normal filter on a pivot table like you can on a regular table.

Turns out that if you create a single header in an empty column next to the pivot table and hit the 'filter' button, it will extend this filter across the pivot table. Feels like a bit of a loophole so I figured I'd post it.

 

[Cindy Ellis]

Thanks for sharing your discovery! I've tried to accomplish the same thing myself, but was never able to come up with something that worked.

 

[Peter_SSs]

Turns out that if you create a single header in an empty column next to the pivot table and hit the 'filter' button, it will extend this filter across the pivot table. Feels like a bit of a loophole so I figured I'd post it.

I don't think you need any extra header. Try just selecting any of the cells down the left of the PT (or a few across the top left) and apply AutoFilter. For me, that applies an AutoFilter over the PT (but the row the filters appear on do seem a little variable).

 

[moonfish]

Peter, my problem with your suggestion is that I tend to have information in the cells above the pivot table. If Excel 'automatically' detects headers, it usually assumes they're in the first row that contains data. I suppose I can try to avoid this habit but it's the best location to place data if the pivot table has to be expanded in the future.

 

[Peter_SSs]

I was just referring to Pivot Tables generally, as I thought you were, not to your particular circumstance since I don't know it. So my comments were really for the general reader.