Getting rid of old Row and Column Labels from the Pivot Table

Pivot tables still remains the go to solution in Excel for Data Analysis.  This article will address the following issue with the Pivot Table command that enables you to still see the leftover entries that no longer exist in the data table. We will also talk about how to remove them…
Here is an Example of what I’m talking about. We have the following data table in Excel.
 

Notice that in the Month column, there are Four Months present, December, January, February and March. Now based on this data, we create a Pivot table where we calculate the Average number per Month.


Nothing out of the ordinary there. Now let’s change the data a bit. What we will do is delete the December data, as we no longer need it, and add the data for April. So now the data table we are working on looks like this
 

Now after refreshing the Pivot Table, we get something like this
 

Now that seems perfectly fine, but let us see what we get in the dropdown menu at Row Labels or Month…
 

And there it is, although our data no longer has any Rows of Data belonging to December, the December is still part of the Month field as you can see. The record was not deleted from the data model. Now this has some great uses as with the GetPivotData function etc. But for this article we will discuss how to get rid of the discarded data in two ways. Manually and using a VBA code or a Macro.

Getting rid of old Row and Column Labels from the Pivot Table manually

You place yourself in the PivotTable and either Right Click and select PivotTable Options or go to the Analyze (Excel 2013) or Options (Excel 2007 and 2010) Tab. In the PivotTable Options dialog box you place yourself on the Data tab.


The command we are looking for is Number of items to retain per field. The value is set to Automatic by Default and that means that Excel will decide how many items to retain. What we need to do is to change that to None and then Refresh the PivotTable.


After doing so, you can clearly see that December has disappeared from the Month field.

Getting rid of old Row and Column Labels from the PivotTable by VBA

If you have more Pivot Tables and you want to do this faster than the option above, you can use the following VBA code…

Sub DeleteOldPivotData()
Dim PivTbl As PivotTable
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
For Each PivTbl In ws.PivotTables
PivTbl.PivotCache.MissingItemsLimit = xlMissingItemsNone
PivTbl.PivotCache.Refresh
Next PivTbl
Next ws
End Sub