Pivot filter

excel3

New member
Joined
Jul 14, 2014
Messages
1
Reaction score
0
Points
0
Hi,

I have a pivot table set up which is showing me all spend by account, Cost center, quarters and then I can drill down to line item for more detail. Works great when I want to filter for a 1,2 or 3 cost centers.

But it fails when I want to look up multiple cost centers. My filter has over 100 unique cost centers. When I need to filter to multiple cost center, I have to go in and tick mark each cost center. sometimes I am looking for details with more than 30 cost centers. Its very annoying to filter them each time. The Cost centers I look for are in no particular range, the numbering is random.

I am sure there is a easy way of doing it which I am not aware of. Can someone please help.

TIA
 
Unless you can use the text filters, like "contains", "begins with", "ends with", etc, then there probably isn't an easier way (unless you get into a VBA solution... to be provided by someone else).
 
…or use slicers, these remain visible and you can just click on what you need.
 
Can you not set groups of cost centres, say set 1 with a,b,c, set 2 with x,y,z etc., and then filter by group? If you used Power Pivot, I am thinking that you could create separate groups and use disconnected slicers on those groups.
 
Can you not set groups of cost centres, say set 1 with a,b,c, set 2 with x,y,z etc., and then filter by group? If you used Power Pivot, I am thinking that you could create separate groups and use disconnected slicers on those groups.

Yes you can if a) your groups of cost centres aren't going to change and b) you can amend the source data. But this approach doesn't lend itself to ad-hoc filtering of groups. Which is certainly the case here:

sometimes I am looking for details with more than 30 cost centers. Its very annoying to filter them each time. The Cost centers I look for are in no particular range, the numbering is random.

Whereas my code is perfect for this. In fact I wrote it for a group of accountants who were filtering cost centres, among other things.
 
Last edited:
Back
Top