"Include new items in manual filter" greyed out when the field is in the filter area of a pivot table

[paulym666]

I'm using Excel in Office 365.

I have a pivot table where one of the fields in the source data can change over time (new products being added). In my pivot I need to exclude just one product, so I have product in the filter with that product deselected.

The problem is that when new product codes come into the data, because one product code is already deselected, all the newly added product codes come into the filter unselected. This makes the data in the pivot incomplete.

How can I ensure that the filter always includes new products?

If I moved the product field into the rows area of the pivot I could check the box to include new items, but that makes the pivot table huge and unwieldy - that doesn't work for my purposes.

Does anyone have any workaround?

Thanks

 

[RoryA]

That option is only disabled for me with 365 if the data has been added to the data model - is that the case for you?

 

[paulym666]

That option is only disabled for me with 365 if the data has been added to the data model - is that the case for you?

Yes - exactly. I can set the filter to exclude the single product and the pivot works. But, over time, if new products get added into the data, these become visible in the filter, but always unchecked and therefore they're excluded from the pivot. But I still want just the one single product to be excluded. If I knew every time that a new product was being added I could manually check the box to make sure it gets included. But I don't know, which means I have to run down the list of products in the filter every time looking to see if any are unchecked. Actually, because they could be dotted around a long list of products and would be easy to miss, the easiest thing to do is just select all and then uncheck the single product that I want to exclude. It's annoying that I have to do this every day - would much rather there was a way to make this happen automatically.

 

[RoryA]

Where is the source data coming from? Can you just filter it out there?

 

[paulym666]

Where is the source data coming from? Can you just filter it out there?

No, it's an OLAP data source (rather than a table of data in a spreadsheet), so I can't filter out upstream.

 

[RoryA]

Not something you can connect to via power query? Might also be worth checking the table properties in Power Pivot. If it's using a SELECT statement, you could alter that to filter out the item you don't want.

Other than that, I think VBA would be the only option.

 

[paulym666]

Not something you can connect to via power query? Might also be worth checking the table properties in Power Pivot. If it's using a SELECT statement, you could alter that to filter out the item you don't want.

Other than that, I think VBA would be the only option.

VBA might work - I tried recording a macro to select all in the filter and then deselect the one product I don't want, but I got a "too many line continuations" error - I guess that there are too many products for VBA to handle - it seems to want to add all except the one I deselected individually.

 

[RoryA]

Can you use a slicer? (either as well as or instead of the filter field)

It makes the coding a lot simpler.