Results 1 to 8 of 8
  1. #1
    Join Date
    Jan 2006

    Unanswered: Removing old field values from Pivot Table

    How do you remove old field values from the Pivot Table drop downs for a field?

    Usually, I can do this by removing the field from the pivot table, refreshing and adding the field back in but it doesn't always work and can become time consuming.



    Value3 is no longer used but still shows in the drop down for Field1

  2. #2
    Join Date
    Oct 2007
    have you worked it out... im having the same problem

  3. #3
    Join Date
    Jan 2006

    Still trying...

    I have not found a solution to this problem yet.

  4. #4
    Join Date
    Oct 2003
    That unfortunately is a characteristic of Pivot tables. Nothing can be done, except recreate the Pivot table from scratch.

    If this becomes a regular/daily problem, then it might be worth checking into VBA code to construct the Pivot when needed, which avoids this problem. And VBA code with Pivots, if designed correctly, can be quite fast.
    old, slow, and confused
    but at least I'm inconsistent!

    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  5. #5
    Join Date
    Oct 2007

    Creating it in VBA coding would be the solution...

    How do I go about creating it in VBA...

    Thanks in advance

  6. #6
    Join Date
    Jan 2009

    Removing old field values from pivot table


    You can use below code for removing old field values from pivot table

    Sub refpivot()
    Dim wbBook As Workbook
    Dim wsSheet As Worksheet
    Dim ptTable As PivotTable
    Dim ptField As PivotField
    Dim ptItem As PivotItem
    Dim i As Long

    Set wbBook = ActiveWorkbook

    On Error Resume Next

    For i = 1 To 2
    For Each wsSheet In wbBook.Worksheets
    For Each ptTable In wsSheet.PivotTables
    For Each ptField In ptTable.PivotFields
    For Each ptItem In ptField.PivotItems
    Next ptItem
    Next ptField
    Next ptTable
    Next wsSheet
    Next i

    On Error GoTo 0
    End Sub

  7. #7
    Join Date
    Feb 2009
    A field will basically act as a filter for your data.

    For each field that you want to use in order to filter your data, you can specify exactly which items to include.

    The page field can only have one field associated with it.

    This is not actually part of the wizard steps ?

    Changing the layout of the Pivot Table is not an actual step in the Wizard as it can be done either before or after the pivot table report has been created.

    1) During the Pivot Table Wizard, pressing the Layout button on Step 3.

    2) Using the PivotTable Field List Pane.

    3) Dragging the fields once the pivot table has been created.

  8. #8
    Join Date
    Oct 2012
    This will keep the values updated to only what is in the data set

    Right-click a cell in the pivot table
    Click on PivotTable options
    Click on the Data tab
    In the Retain Items section, select None from the drop down list.
    Click OK, then refresh the pivot table.

Posting Permissions

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