In a pivot table, you can apply a manual filter to a pivot field, by using the check boxes in the field’s drop down list.
In this example, there is a date field in the Row Labels area and a few dates have been selected in the manual filter.
Updating the Pivot Table Data
If you add new records in the pivot table’s source data, new dates might be added. When you update the pivot table, the new dates might appear, even if you hadn’t selected those dates in the manual filter.
Seeing the new dates could be helpful, if you want to make sure that you notice new records when they’re added. You can manually deselect the new items after they appear.
However, if you want to prevent the new dates from automatically appearing, you can change a setting in the pivot field, to specify if new items are included or not, when the field is manually filtered.
Change the Pivot Field Setting
To change the setting, and prevent new items from being included, follow these steps:
- In the pivot table, right-click a cell in the date field, and click Field Settings.
- On the Subtotals & Filters tab, in the Filter section, remove the check mark from Include New Items In Manual Filter
- Click OK.
____________
Thanks for the info. Very effective information. Especially i havn’t noticed the include new items check box unless i run across your blog
My Field Setting for “Include new items in manual filter” appears to be a default of “Unchecked”. How can I make it default to “Checked” as I want always to include additional data in the refresh?
Debra- I REALLY appreciate your site, it has helped me out on many projects!
Thank you!
-BillS
@Bill Thanks! You can’t change that as a default setting so you’ll have to change the fields manually or create a macro to do it.
@Bill – You need to save the file in one of the new file formats. The feature is not enabled with “Excel 2003 combatible” files.
Hi Debra, I have some pivot tables which the source is from a PowerPivot, but I can’t check or uncheck the “Include New Items In Manual Filter” option because it appear grey out or disabled. How can I fix it?
Thanks
Joe
I am also having the same problem. My field setting option is disabled in my excel. Please advise
Hi Debra, Thanks for your helpful site! I’m not sure if you are still answering questions on this topic, but here’s hoping, please …
Despite the fact that I have a check mark against ‘Include New Items In Manual Filter’ setting for my row pivot field (labelled “Purchaser”), while the new value appears in the field’s drop down filter list, it is unchecked, so the data from the new record in the source table does not actually appear in the pivot table. This appears to be the opposite to your statement that “When you update the pivot table, the new dates might appear, even if you hadn’t selected those dates in the manual filter.” Is there a way I can ensure that the data from new records that have new items for the filtered row field (“Purchaser”) is automatically checked on so that it is included in the pivot table?
Thanks, Nick
@Nick, thanks for describing the problem with your pivot table, and I’m not sure why that would happen.
What version of Excel are you using? Does this happen with all pivot tables, or just this one?