Delete Pivot Table is the pivot table management solution that ensures your Excel workspace remains clutter-free and focused on the data that matters most. This invaluable feature simplifies the removal of pivot tables, allowing you to streamline your spreadsheets effortlessly. With ‘Delete Pivot Table,’ you regain control over your data presentation, making room for clearer insights and more efficient data analysis. Embrace the simplicity and precision of this function to transform your Excel experience, ensuring that your pivot tables serve your evolving data needs. Whether you’re cleaning up your worksheets or reorganizing your data models, ‘Delete Pivot Table’ is your trusted ally for maintaining a clutter-free, efficient, and productive Excel environment.
To swiftly process thousands of rows of data and generate fast summaries out of it, you don’t need to know any formulas or coding. Simply being able to drag and drop and having a basic understanding of pivot tables will do.
We’ll demonstrate several methods to delete a pivot table from Excel in this article.
This Tutorial Covers:
- How to delete Pivot Table and the Resulting Data?
- How to delete a Pivot Table Using Keyboard Key?
- How to remove Pivot Table but Keep the Resulting Data?
- How to delete the Resulting Data but Keep the Pivot Table?
- How to delete All Pivot Tables in One Go?
- How to Refresh Pivot Table in Excel?
- Manually refresh Pivot Table:
- Refresh Pivot Table on Protected Sheet:
- Refresh/Update Pivot Table by Changing the Data Source:
- Autor refresh Pivot Table Using a VBA Macro:
How to Delete a Pivot Table in Excel along with Data?
In the example below, I create a pivot table and utilize it to determine the Sum of Revenue for several buyers (to which I will be referring to as Pivot Table summary data in this tutorial).
The procedures to remove pivot table and any summary data are listed below:
Step 1: In the Pivot Table, choose any cell.
Step 2: To begin, select the “PivotTable Analyze” tab on the ribbon. Only when a cell in the pivot table has been selected will this contextual tab emerge. Click the “Select” option under the “Actions” group to select the whole table.
Step 3: Click on “Entire Pivot Table”.
Step 4: This will select the entire pivot table and press the “Delete” key on your keyboard.
The entire Pivottable would be deleted using the procedures above.
How to delete the Pivot table Using Keyboard Key?
Using the Delete key on your keyboard will enable you to quickly remove a PivotTable from your spreadsheet.
The procedures to remove the pivottable using the keyboard key:
Step 1: In the Pivot Table, choose any cell.
Step 2: Next, select the full pivot table using the keyboard shortcut “Control + A.” Control A won’t select the complete pivot table if filters have been applied to it. You must employ the technique described above (or select the Pivot Table manually).
Press the “Delete” key on your keyboard while your table is chosen.
The Pivot Table would be deleted using the procedures above.
How to delete Pivot Table but Keep the Data?
There may be circumstances in which you want to delete the pivot table but keep the data that was obtained as a result of using the pivot table. This can occur if you utilize a pivot table and only wish to transmit the data that results to your boss or client.
This may also be required if your pivot table is too large and is making your worksheet look bloated. Such a Pivot table can significantly minimize the size of the Excel file.
For instance, in the example below, I want to eliminate the pivot table but maintain the information in cells A1:C7.
The procedures to remove the pivot table but keep the resulting data are described below:
Step 1: In the Pivot Table, choose any cell.
Step 2: To begin, select the “Analyze” tab on the ribbon. Only when a cell in the pivot table has been selected will this contextual tab emerge. Click the “Select” option under the “Actions” group.
Step 3: Click on “Entire Pivot Table”.
Step 4: In the chosen Pivot Table, right-click any cell. Then select “Copy”. This will replicate every piece of pivot table data.
Step 5: On the “Home” tab, click. Choose “Paste” from the menu. Pick the first icon in the “Paste Values” section (which is of Paste as Value).
The Pivot Table would be deleted as a result of the actions above, but the data would remain.
Note: Copying data from one Pivot Table and pasting it somewhere else in the worksheet (Control V) just results in the creation of another Pivot Table. Make sure to paste as values if you only want to maintain the final data and not the Pivot Table.
How to delete the Data but Keep the Pivot Table?
Let’s say you have summarized the data in a pivot table.
You can also choose to keep the pivot table and merely remove the data you currently have so that you can reorganize it and generate another summary.
The Pivot Table will also be deleted if you select the data and press the delete key.
The steps to preserve the pivot table and only remove the output data are as follows:
Step 1: In the Pivot Table, choose any cell.
Step 2: In the ribbon, select the “Analyze” tab. This is a contextual tab that only appears once you have selected a Pivot Table cell. Select “Clear” under the Actions group. Select “Clear All” by clicking.
The data would be deleted as a result of the actions above, but the Pivot Table format would remain.
How to delete All Pivot Tables in the Entire Workbook?
If you have numerous Pivot tables and wish to delete them all at once, you may do so with a straightforward VBA code.
Precaution: You cannot retrieve these Pivot Tables once you delete them using the VBA code. So, when you do this, be completely certain, and if necessary, make a backup copy to prevent losing these.
The VBA code that will erase all Pivot Tables at once is provided below:
Sub DeleteAllPivotTables()
Dim Ws As Worksheet, Pt As PivotTable
On Error Resume Next
For Each Ws In ActiveWorkbook.Worksheets
For Each Pt In Ws.PivotTables
Ws.Range(Pt.TableRange2.Address).Delete Shift:=xlUp
Next Pt
Next Ws
End Sub
The For-Each-Next loop is used in the coding above to cycle through each worksheet in the open workbook. It determines whether or not there is a pivot table in each worksheet before deleting it (if there is).
Where to Put this VBA code?
In the VB Editor, this code must be added to the usual module.
These are the procedures to insert this code into the module:
Step 1: Open the workbook from which the pivot tables should be deleted.
Step 2: Use “ALT + F11” to quickly navigate (this opens the VBA Editor window).
A project explorer can be seen on the left side of this VBA Editor window (where all the workbooks and worksheets are listed). In the workbook, right-click any object you wish this code to affect.
Place your cursor on “Insert.” Then select “Module.” By doing this, a new module will be added to the current workbook.
Step 3: Copy the aforementioned code, then paste it in the module window (which will show up on the right).
Step 4: By pressing the green button in the VBA Editor toolbar, you can now run this VBA code.
When you run this code, all the Pivot tables will be eliminated from every worksheet in the workbook.
For ready-to-use Dashboard Templates:
