How to Remove Old Row and Column Items from the Pivot Table in Excel?
Last Updated :
21 Mar, 2022
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.
Similar Reads
How to Remove Pivot Table But Keep Data in Excel?
In this article, we will look into how to remove the Pivot Table but want to keep the data intact in Excel. To do so follow the below steps: Step 1: Select the Pivot table. To select the table, go to Analyze tabSelect the menu and choose the Entire Pivot Table. Step 2: Now copy the entire Pivot tabl
1 min read
How to Find the Last Used Row and Column in Excel VBA?
We use Range.SpecialCells() method in the below VBA Code to find and return details of last used row, column, and cell in a worksheet. Sample Data: Syntax: expression.SpecialCells (Type, Value) Eg: To return the last used cell address in an activesheet. ActiveSheet.Range("A1").SpecialCells(xlCellTyp
2 min read
How to Prevent Grouped Dates In Excel Pivot Table?
We may group dates, numbers, and text fields in a pivot table. Organize dates, for instance, by year and month. In a pivot table field, text elements can be manually selected. The selected things can then be grouped. This enables you to rapidly view the subtotals in your pivot table for a certain gr
3 min read
Power BI : Hiding tables, columns, and fields from Power Pivot
A robust data modeling tool in Power BI called Power Pivot enables users to build data models by fusing several data sources, building relationships, and including computed columns and metrics. But occasionally you might want to exclude some tables, columns, or fields from the Power Pivot view in Po
4 min read
How to Sort a Pivot Table in Excel
Sorting a Pivot Table in Excel is a powerful way to organize and analyze data effectively. Whether you want to sort alphabetically, numerically, or apply a custom sort in Excel, mastering this feature allows you to extract meaningful insights quickly. This guide walks you through various Pivot Table
7 min read
How to Create Pivot Chart from Pivot Table in Excel using Java?
A Pivot Chart is used to analyze data of a table with very little effort (and no formulas) and it gives you the big picture of your raw data. It allows you to analyze data using various types of graphs and layouts. It is considered to be the best chart during a business presentation that involves hu
4 min read
How to freeze rows and columns in Excel
Navigating large datasets in Excel can quickly become overwhelming, especially when key headers or reference columns disappear as you scroll. That’s where the freeze panes feature comes in. Freeze panes in Excel allow you to lock specific rows or columns in place, ensuring critical data remains visi
10 min read
How to Freeze Rows and Columns in Excel: Step-by-Step Guide
The freeze panes feature in Excel is invaluable for keeping key rows or columns visible while scrolling through large datasets. However, there are times when frozen panes can hinder flexibility, such as when you need to view your entire spreadsheet without locked rows or columns. Knowing how to unfr
4 min read
How to Group and Ungroup Pivot Chart Data Items in Excel?
A pivot chart is the visual representation of a pivot table in Excel. Pivot charts and pivot tables are connected with each other. Pivot chart are much more flexible than normal chart because Pivot Chart is linked to a PivotTable. Filters, sorts, and data rearrangements applied to Pivot Table are re
2 min read
How to Delete a Pivot Table in Excel?
A pivot table is a tool in Excel that allows you to quickly summarize data in the spreadsheet. When it comes to deleting a Pivot Table, there are a few different ways you can do this. The method you choose will depend on how you want to delete the Pivot Table. 1.Delete the Pivot Table and the Result
3 min read