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

Published on
9,012 Points
3 Endorsements
Last Modified:
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
Refreshed Data
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…
Mont Field
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.

PivotTable Options
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.

Refreshed 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
Next PivTbl
Next ws
End Sub

Expert Comment


Expert Comment

Thanks for posting this article Gasper.  It was exactly what I needed when I need it.


Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Join & Write a Comment

This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month