CODEWITHSUNDEEP
excelpivot-table
GeoffDS
GeoffDS

Reputation: 1271

Obsolete items in pivot table list

In a pivot table of mine, when I go to filter the data using the Row Label, where it shows the checkbox list where you can select one or many or all items to be included, this list includes items that no longer exist. Or alternatively, if you go to the PivotTable Field List and select the field and try to filter there, the same thing shows up.

That is, I used to have a certain item in that column in my Excel spreadsheet (the source for the pivot table) and a month ago I stopped using that certain item, so it no longer appears at all in the data source. But, it still shows up in the checkbox list for the Row Label in the pivot table. How can I remove these? Refreshing the pivot table does not fix this. There are already a lot of different boxes and this just makes it harder to read.

Thanks for any help

Upvotes: 17

Views: 39528

Answers (2)

Banjoe
Banjoe

Reputation: 1768

If this is something you frequently encounter in the pivot table consider creating a VBA routine to remove old items.

  1. Press Alt-F11 to access the VBA editor
  2. In the project explorer window double click "ThisWorkbook"
  3. In the top two dropdowns above the code window select "Workbook" on the left and "Open" on the right.

  4. Paste the following code from adapted from Excel Pivot Table Tutorial -- Clear Old Items

    Private Sub Workbook_Open()

    'prevents unused items in non-OLAP PivotTables
    'pivot table tutorial by contextures.com
    Dim pt As PivotTable
    Dim ws As Worksheet
    Dim pc As PivotCache

    'change the settings
    For Each ws In ActiveWorkbook.Worksheets
      For Each pt In ws.PivotTables
        pt.PivotCache.MissingItemsLimit = xlMissingItemsNone
      Next pt
    Next ws

    'refresh all the pivot caches
    For Each pc In ActiveWorkbook.PivotCaches
      On Error Resume Next
      pc.Refresh
    Next pc

End Sub

This will remove any old items every time the workbook is opened, assuming macros are enabled.

Upvotes: 4

AlejandroR
AlejandroR

Reputation: 5191

Taken from http://www.contextures.com/xlPivot04.html

Clear Old Items from a Pivot Table in Excel 2007

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

Upvotes: 31

Related Questions