How to Remove Old Row and Column Items from the Pivot Table in Excel?

Pivot table is one of the most efficient tools in excel for data analysis. If you are using pivot tables frequently, then you will find even after deleting the old data from the data source, it remains in the filter drop-down of the pivot table. We will learn, how to remove the old row and column items from the pivot table in excel. 

Reason for not removal of the old data

To better understand, why the data is not removed in the pivot table, even after removing it from the source data, you need to understand the architecture of the pivot table. Internally, the pivot tables have a pivot cache, where the copy of the source data is held. This is done to faster the data retrieving process from the source to the pivot table.

Whenever you refresh the pivot table, it gets updated from the pivot cache and not the source data. So, the pivot cache has its properties and default behavior. One of the default behavior, of pivot cache, is that it does not delete the old rows or columns from the filter drop-down. It is a default setting which can be changed very easily. 

Removing Old rows and columns from the Pivot table 

Given a table students and their marks. A pivot table is also made from the given table. It represents the students and their total marks obtained. 

Step 1: Deleting the sixth row from the given table i.e. the student name Shubham. 

Step 2: The Student named Shubham got deleted from the table and the total rows remaining are eight. You can also see that the Shubham is not deleted from the pivot table. This specifies that the pivot table is not updated instantaneously.

Step 3: To update the pivot table, right-click inside any cell of the pivot table. For example, right-click inside cell C6, cell value Arushi. A drop-down appears. Click on the refresh button.

Step 4: The student named Shubham got removed from the pivot table. Hence, the table is updated. Now, we have resolved one problem. 

Step 5: Click on the filter button, in the pivot table of cell G5. A drop-down appears. You can see, even after refreshing the pivot table, the student named Shubham still appears in the drop-down filter menu. This is not correct. 

Step 6: To resolve this problem, right-click inside any cell of the pivot table. For example, right-click inside cell C6, cell value Arushi. Click on the PivotTable Options. 

Step 7: PivotTable Options dialogue box appears. 

Step 8: Go to the Data tab. Under, Retain items deleted from the data source section, go to Number of items to retain per field.

Step 9: Click on the drop-down and select None. Click Ok.   

Step 10: Right-click inside any cell of the pivot table. For example, right-click inside cell C6, cell value Arushi. A drop-down appears. Click on the refresh button.

Step 11: Click on the filter button, in the pivot table of cell G5. A drop-down appears. The Student named Shubham is removed from the filter drop-down. Hence, the problem is resolved. 

Removing Old rows and Columns in Pivot table using VBA Macro

You can do the same thing with VBA Macro. Write the following code in the VBA code editor and run it. It will change the Number of items to retain per field from Automatic to None. In the below example, there are two for loops. The outer loops state the condition to traverse all the worksheets in the entire workbook. The inner loop states to traverse to all the pivot tables in a worksheet. The statement pivottables.PivotCache.MissingItemsLimit = xlMissingItemsNone changes the Number of items to retain per field from Automatic to None. 

Run the above code written. Consider the same data set, as seen in the first method. Check whether the  Number of items to retain per field is set to None or not. Following are the steps: 

Step 1: Right-click inside any cell of the pivot table. For example, right-click inside cell C6, cell value Arushi. A drop-down appears. Click on the refresh button.

Step 2: PivotTable Options dialogue box appears.  Go to the Data tab. Under, Retain items deleted from the data source section, go to Number of items to retain per field. We can see it is set to None.