[XL 2007] Filter field list in pivot table

[rafaelsgarbulho]

Guys, a simple pivot table in excel 2007 has some fields that I filter (like subfields) however the last fields keep displaying all values into the fields list like ignoring previous filters, is there an easy way to fix it? Help me out!

 

[Jerry Sullivan]

Hi and Welcome to MrExcel,

I think you're referring to the way the manual filters (checkboxes) for each field will display all the pivot items regardless of whether the other filter selections currently prevent those items from being shown. This is different that the behavior of AutoFilters which only list the visible subset of all items.

Unfortunately, that's the way PivotTable manual filters are designed. There isn't a setting or simple work around to have the checkboxes shown conditionally.

 

[rafaelsgarbulho]

Hi Jerry, Super thanks for your explanation
How about coding a solution for that, has anyone tried that before?

 

[Jerry Sullivan]

I'm not aware of anyone trying that. The Object Model doesn't contain any properties or methods for the manual filters, so outside of modifying the Excel source code, I don't think it's possible to code a solution that causes the PivotTable's manual filters to have that behavior. The only coding approaches with any feasibility that I can think of at the moment are:

1. Create filter objects that reside outside of the PivotTable.
2. Modify the PivotCache to exclude records that can't be displayed due to other filters currently applied.

Those approaches would be clunky and difficult to execute and IMO wouldn't provide enough benefit to justify the effort.
Slicers, which became available with xl2010 have some similarities to approach 1. They physically reside outside PivotTables and they show pivot items that can be displayed at the top of the list in a different color than those items that can't be displayed.