If you apply a filter to a PivotTable, then only the filtered items will be visible. If you refresh the PivotTable at a later time, the filter is saved, and new items are not visible. In most cases, this is logical, but in some cases it can be useful to automatically add these new items to the applied filter.
This question came from a participant in an Excel course. She had a list of products, and some products were no longer available. Consequently, she had filtered out these products, but when a new product was added to the list, the new product was also filtered out. Naturally, it would have been easy if the table had a ‘Status’ column that could be filtered, but as this was not available, a different solution had to be found.
We will use this simple table:
As some products are no longer available, we need to filter them out.
This gives us the following result:
Suppose we now add a new product (Product 11) to the list and we refresh the PivotTable then it will appear as if nothing has changed. However, you’ll see the new product in the filter, but it’s not selected.
To add new items to the filter automatically:
- Open the Field Settings of the product field. (Right-click on Product and choose ‘Field Settings’)
- Activate the option ‘Include new items in manual filter’
Because Product 11 is already in the data source of the PivotTable you will need to add this product manually by changing the filter. However, new products will now be added to the PivotTable by default. In the image below, products 12, 13, 14 and 15 have been added to the table and the PivotTablehas been refreshed.
Want more Excel tips like this? In our Excel training courses we can show you lots more. Or call one of our Excel experts who will coach you and help you optimise your Excel documents.
