+ Reply to Thread
Results 1 to 4 of 4

Refresh of pivot column filter dropdowns when changing the source data

  1. #1
    Registered User
    Join Date
    09-22-2012
    Location
    Auckland, New Zealand
    MS-Off Ver
    Excel 2010
    Posts
    9

    Refresh of pivot column filter dropdowns when changing the source data

    I have a pivot built off a large dataset.
    On the pivot, each column has the drop-down selector to filter the values.
    If I reduce the source data, deleting certain rows... e.g. remove all rows contain certain states say CA, TX, PA... then when I refresh the pivot, I would expect the drop down filter not to display CA/PA/TX. But it continues to display these as valid items to filter on, even though they are not.

    Obviously I have checked the source data, and I have used the dropdown filter on these and it selects no data in the pivot, so how can I get the now non-valid values to disappear off the filter dropdown?

    TIA.

    Ralph in Auckland
    Last edited by rmg08057; 10-04-2012 at 11:18 PM.

  2. #2
    Forum Expert
    Join Date
    06-18-2004
    Location
    Canada
    MS-Off Ver
    Office 2016
    Posts
    1,474

    Re: Refresh of pivot column filter dropdowns when changing the source data

    Try the following...

    Right-click the PivotTable > PivotTable Options > Data > Number of items to retain per field > None

  3. #3
    Registered User
    Join Date
    09-22-2012
    Location
    Auckland, New Zealand
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: Refresh of pivot column filter dropdowns when changing the source data

    Ah ha! It works. Many Thx.

  4. #4
    Forum Expert
    Join Date
    06-18-2004
    Location
    Canada
    MS-Off Ver
    Office 2016
    Posts
    1,474

    Re: Refresh of pivot column filter dropdowns when changing the source data

    You're very welcome!

    Cheers!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1