• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Report filter in Pivot table

K

KiKi

Member
I have a pivot table. On top of that there are 4 report filters. When i choose top filter, i want 2nd report filter should only show options based on first filter. Currently 2nd/3rd/4th report filters shows everything in there irrespective of 1st filter chosen.

Is there any way to do that/

Thank !!
 
If you've got Excel 2013 then slicers let you do that...you just have to check the option ‘Hide items with no data’.

PivotTables don't let you do that natively, which is a real shame. But I'm in the process of adding some functionality via some VBA code that lets users do this. (I'm writing this up for my book that I'm nearly finished, and I'll also have a commercial add-in for sale soon that also lets you do this easily. )

If you like, you can flick me a line at weir.jeff@gmail.com and I can let you know when I've got the code working. (Shouldn't be too far away)
 
Sorry, badly worded.

While Excel 2010 has Slicers, I understand they don't have that option "Hide items with no data".

So in Excel 2013, Slicers let you accomplish something similar to what Kiki wants. But in Excel 2010, they don't...because they still show all items. (Although they may appear lighter if they aren't relevant given the current selection. I can't recall)
 
Hi Jeff ,

I don't have Excel 2013 , but in Excel 2010 , there is a checkbox labelled :

Visually indicate items with no data

and another one labelled :

Show items with no data last

which to a user should signify that selecting such items will not be useful.

Narayan
 
True. But it's still pretty messy, compared to 2013.

Here's an example, with "Hide items with no data" checked:
Clean slicers.gif

...and here it is with the 'Visually indicate items with no data' checked:
Visually Indicate.gif
One does pretty much what Kiki wants. The other gives an indication for sure, but it's nowhere near as good.
 
So I'm getting close to finishing a pretty cool add-in that does this. It's a cross between a slicer and the existing PivotTable Filter Dialog box. You launch it simply by double clicking a PivotTable Field Column Header, and you get something like this:
Viewable.gif

Note the 'Show Viewable Items' radiobutton in the first set of controls. With that checked, then you only see the list of things that are currently viewable in the PivotTable, instead of having to see ALL items whether they are actually viewable or not as you get with the native filter.

But if you click the 'Show All Items' button, then you can see all of them if you want:
All.gif

And if you want to invert the filter selection, just click Invert Filter:

Invert.gif

Note that all these settings are replicated in the underlying PivotTable. So this baby lets you control a PivotTable much more easily than the native settings. Plus the Invert Filter and Filter To Match Range options are killer.

You can also look at just the filtered items or just the unfiltered items too if you want, by clicking the 'Show Filtered Items' or 'Show Unfiltered Items' buttons. Plus you can do crazy wildcard searches that can support multiple exclusions (I haven't quite finished this yet, but I'm close).

So it lets you do a heck of a lot more than what you can do natively.
 
You must log in or register to reply here.
Back
Top