[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More


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

Published on
9,071 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

Exploring SharePoint 2016

Explore SharePoint 2016, the web-based, collaborative platform that integrates with Microsoft Office to provide intranets, secure document management, and collaboration so you can develop your online and offline capabilities.

Join & Write a Comment

This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month