Include New items in Pivot Table Filter

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.

PivotManualFilter01

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:

  1. In the pivot table, right-click a cell in the date field, and click Field Settings.
  2. On the Subtotals & Filters tab, in the Filter section, remove the check mark from Include New Items In Manual Filter
  3. Click OK.

PivotManualFilter02

____________

8 thoughts on “Include New items in Pivot Table Filter”

  1. Thanks for the info. Very effective information. Especially i havn’t noticed the include new items check box unless i run across your blog

  2. 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

  3. @Bill – You need to save the file in one of the new file formats. The feature is not enabled with “Excel 2003 combatible” files.

  4. 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

  5. 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

    1. @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?

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.