Active filter on pivot prevents new data source items from appearing

[gravanoc]

If I have a pivot table with country name and exports and I decide to filter out France, then if any new country names are added to the original data source, they will not automatically appear after refreshing the pivot table. This seems to be the case as long as an active filter is present. It seems to arbitrarily decide that all future additions should also be filtered out until the filter is removed on all country names. Is there any option to prevent this behavior, so that only the items that were designated to be filtered out will be affected without affecting future additions automatically?

 

[Alex Blakenburg]

Its not arbitary but it is a pain.
What happens when you use the tick box filter option and you unselect something, Excel only records what is selected and the next time something is added it comes in as unselected. It is something to watch out for in Power Query as well although it is normally fairly easy to fix in PQ.

What you can do is change the Field setting.
Right click on the field and go to Field Setting.
Under Subtotals & Filters tick the box Include new items in manual filter.

If your field is in the body of the pivot ie not in the Page/Report Filter, another option is instead of unticking the item use the Label Filter and use Not Equal to or Does not Contain.

 

[gravanoc]

Its not arbitary but it is a pain.
What happens when you use the tick box filter option and you unselect something, Excel only records what is selected and the next time something is added it comes in as unselected. It is something to watch out for in Power Query as well although it is normally fairly easy to fix in PQ.

What you can do is change the Field setting.
Right click on the field and go to Field Setting.
Under Subtotals & Filters tick the box Include new items in manual filter.

If your field is in the body of the pivot ie not in the Page/Report Filter, another option is instead of unticking the item use the Label Filter and use Not Equal to or Does not Contain.


View attachment 110478

Thank you.