Search Contextures Sites

Excel Pivot Table Tutorial -- Clear Old Items

Manually Clear Old Items Change the Retain Items Setting in Excel 2007 Programmatically Clear Old Items -- Excel 2002 and later Programmatically Clear Old Items -- Excel 97/2000 Download the zipped sample file for this pivot table tutorial	Table of Contents	Table of Contents

Old Items Remain in Pivot Field Dropdowns The data in the pivot table source may change, and items that were previously in the source are no longer included. For example, some sales reps may leave the company, and the names of their replacements appear in the source table. Even after you refresh the pivot table, the names of the old sales reps will appear, along with the new names. In the list at right, Cartier has replace Gill, but Gill still appears in the list. This pivot table tutorial shows how you can clear the old items either manually or programmatically.

Manually Clear Old Items To manually clear the old items from the list: If you manually created any groups that include the old items, ungroup those items. Drag the pivot field out of the pivot table. On the Pivot toolbar, click the Refresh button Drag the pivot field back to the pivot table

Change the Retain Items Setting in Excel 2007 To prevent old items from being retained in an Excel 2007 pivot table, you can change an option setting: 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. To view the steps in a short video, click here

Programmatically Clear Old Items -- Excel 2002 or later In Excel 2002, and later versions, you can programmatically change the pivot table properties, to prevent missing items from appearing, or clear items that have appeared. Sub DeleteMissingItems2002All() '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

Programmatically Clear Old Items -- Excel 97/Excel 2000 In previous versions of Excel, run the following code to clear the old items from the dropdown list. Sub DeleteOldItemsWB() 'pivot table tutorial by contextures.com 'gets rid of unused items in PivotTable ' based on MSKB (202232) Dim ws As Worksheet Dim pt As PivotTable Dim pf As PivotField Dim pi As PivotItem On Error Resume Next For Each ws In ActiveWorkbook.Worksheets For Each pt In ws.PivotTables pt.RefreshTable pt.ManualUpdate = True For Each pf In pt.VisibleFields If pf.Name <> "Data" Then For Each pi In pf.PivotItems If pi.RecordCount = 0 And _ Not pi.IsCalculated Then pi.Delete End If Next pi End If Next pf pt.ManualUpdate = False pt.RefreshTable Next pt Next ws End Sub Download the zipped sample file for this pivot table tutorial

Pivot Table Tutorials Excel Pivot Table -- Introduction  Excel Pivot Table -- Clear Old Items Excel Pivot Table -- Create a Pivot Table in Excel 2007  Excel Pivot Table -- Custom Calculations  Excel Pivot Table -- Data Field Layout Excel Pivot Table -- Dynamic Data Source Excel Pivot Table -- FAQs Excel Pivot Table -- Field Settings Excel Pivot Table -- Filter Source Data   Excel Pivot Table -- Filters, Top 10  Excel Pivot Table -- GetPivotData Excel Pivot Table -- Grand Totals Excel Pivot Table -- Grouping Data Excel Pivot Table -- Layout, Excel 2007 Excel Pivot Table -- Multiple Consolidation Ranges Excel Pivot Table -- Pivot Cache    Excel Pivot Table -- PivotTable Style Excel Pivot Table -- Printing    Excel Pivot Table -- Protection   Excel Pivot Table -- Report Filters Excel Pivot Table -- Running Totals   Excel Pivot Table -- Show and Hide Items  Excel Pivot Table -- Sorting Excel Pivot Table -- Subtotals  Excel Pivot Table -- Summary Functions Excel Pivot Table -- Unique Items

Pivot Table Books Pivot Tables, Beginning (Excel 2007)  Pivot Tables, Recipe Book (Excel 2003)  Pivot Tables, Recipe Book (Excel 2007)  Pivot Table Add-Ins Pivot Tables - Add-in -- Pivot Power  Pivot Tables - Add-in - Pivot Play PLUS  Pivot Table Videos Pivot Tables - Clear Old Items Pivot Tables - Copy a Custom PivotTable Style Pivot Tables - Create in Excel 2007 Pivot Tables - Create from Multiple Sheets Pivot Tables - Data Field Layout Pivot Tables - Date Filters, Add Pivot Tables - GetPivotData Pivot Tables - Group Data Pivot Tables - Layout, Excel 2007 Pivot Tables - Report Filters, Add Pivot Tables - Running Totals Pivot Tables - Select Sections Pivot Tables - Subtotals, Create Multiple Pivot Tables - Top 10 Filters

 

Home     Excel Tips     Excel Files     The Excel Store     Blog     Contact
Privacy Policy		Contextures Inc., Copyright ©2010 All rights reserved.	Debra Dalgleish