If, like me you re use Pivot Tables over and over again for a template or your data source changes, like products no longer being sold or one of your sales reps no longer working for you, you may find that even when your data is refreshed that old items remain in the Table Field drop downs.
From Excel 2007 onwards it is really quite simple to remove these old items, we can use Change Retain Items function.
- Right click a cell in your data table
- Pivot Table options
- Select Data Tab
- In Retain Items Section select None in the drop down box
- Hit ok
- Refresh your Pivot Table
This is a really easy way to keep your data all fresh a relevant. In earlier i.e from Excel 2002 onwards versions of Excel there are two ways to clear any old items. For a quick video on this function click here.
Firstly if you manually created any groups that contain your old items you need to
- Ungroup these items
- Drag the pivot field out of the pivot table
- Hit the refresh button on the pivot table toolbar
If you want to use some simple Visual Basic (VB) code then you can use this following routine to programmatically remove old items form your pivot tables.
Sub DeleteUnused PivotTableItems()
‘removes unused items in PivotTables
'pivot table tips by how to excel at excel dot com Dim pt As PivotTable Dim ws As Worksheet Dim pc As PivotCache 'changes the settings For Each ws In ActiveWorkbook.Worksheets For Each pt In ws.PivotTables pt.PivotCache.MissingItemsLimit = xlMissingItemsNone Next pt Next ws 'refreshes all the pivot caches For Each pc In ActiveWorkbook.PivotCaches On Error Resume Next pc.Refresh Next pc End Sub
To get this routine to work you need to create this VB routine. It is simple to do.
- Open your Vb editor- quickest way to do this is to hit ALT+F11
- In project explorer double click on ‘This Workbook’
- Above the code in the two drop down boxes select Workbook on the left
- Select Open on the right
- Copy the above code except the Sub and End Sub lines
Paste the code between the
Private Sub Workbook_Open() and End Sub Lines
- Hit ALT+ F11 to close the VB editor again
This will refresh your pivot tables and clear any old items that are no longer relevant to your data set once your workbook is opened. You need to ensure that macros are enabled for the routine to work.