A Pivot Table is an Excel tool that helps we quickly summarize and analyze large datasets. However, there are times when we may want to remove or reset a Pivot Table. Whether we want to delete just the Pivot Table, retain the values, or remove multiple Pivot Tables at once, this guide covers four effective methods.
1. Delete the Pivot Table and the Resulting Data
Steps to delete the Pivot table and the Resulting Data
- Select any cell in the Pivot Table
- Select the ‘Analyze’ tab.
- Click on the ‘Select’ option in the Actions group.
- Choose Entire Pivot table
- Hit the Delete key.
2. Delete the Pivot Table but Keep the Resulting Data
If we want to remove the Pivot Table structure but retain the current values, we can copy-paste the values before deleting.
Steps to delete the Pivot table but Keep the Resulting Data:
- Click on any cell inside the Pivot Table.
- Choose the ‘Analyze’ tab in the ribbon.
- In the Actions group, choose the ‘Select’ option.
- Select the Entire Pivot table.
- Right-click on any cell of the selected Pivot Table.
- Click on Copy. This will copy the data of the entire Pivot Table.
- Click the Home tab. Click on the Paste option. In the Paste Values section, click on the first icon.
The above steps would delete the Pivot Table but still keep the resulting data.
3. Delete the Resulting Data but Keep the Pivot Table
Below are the steps to keep the Pivot table and remove the resulting data only:
- Select any cell in the Pivot Table
- Choose the ‘Analyze’ tab in the ribbon.
- Select the ‘Clear’ option in the actions group. Choose the ‘Clear All’ option.
4. Delete All Pivot Tables in One Go(Using VBA)
If our workbook contains multiple Pivot Tables, deleting them one by one can be time-consuming. we can automate the process using a simple VBA script.
VBA Code to Delete All Pivot Tables:
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
This code needs to be placed in the regular module in the VB Editor
Below are the steps to put this code in the module:
- Open an Excel sheet.
- Use the shortcut ALT + F11(it will open the VBA Editor window).
- In this VBA Editor window, on the left, there is a project explorer. Right-click on any object in the sheet where we want this code to work.
- Hover the cursor on Insert. Click on Module. This will insert a new module for the current worksheet.
- In the module window, write VBA code, as the code will run it will remove all pivot tables.
