How to Delete or Remove a Pivot Table in Excel

Once you become a pro at creating reports and data analysis with pivot tables, you might need to delete a pivot table to make new reports.

Reset a Pivot Table Instead of Removing It

Just follow the quick steps below to reset all the pivot table fields.

1. First, click anywhere on the pivot table or select the entire pivot table.
2. Now, go to the analyze tab.
   
3. Here click on the little button next to clear.
   
4. At last, click on “Clear All”.
   

When you click the “Clear All” option button, you will get a blank pivot table again.

This option doesn’t delete a pivot table completely but removes all the rows and columns you used to build it.

• Select the Pivot Table: Select a cell within the pivot and press Ctrl + A on your keyboard twice. The first press will select the pivot table’s data area, and the second will select the entire pivot, including the headers.
• Copy the Pivot Table: You should copy it once the pivot table is selected. Press the keys Ctrl + C on your keyboard to copy the pivot table. This action will copy all the contents of the pivot table, including all its data and formatting.
• Select the Paste Location: After copying the pivot table, you need to select the location where you want to paste the values. To do this, click on the cell where you want the data to start.
• Paste As Values: Under the Home tab, look for the Paste button and click on the arrow below it. Select the Paste Values option from the dropdown menu. This option will paste the data from the pivot table as hard values without any of the pivot table functionalities.

When using the method, it is essential to delete and paste the pivot table as values so that we will only get the values, not the pivot table. You can also use the keyboard shortcut Ctrl + C to copy and Alt > H > V > V.

Delete All the Pivot Tables with a Macro (VBA Code)

To run the below code, in the developer tab, open the visual basic editor and then paste it into the code window:

Sub RemoveAllthePivotTables()
Dim mySheet As Worksheet
Dim myPivot As PivotTable
On Error Resume Next
For Each mySheet In ActiveWorkbook.Worksheets
    For Each myPivot In mySheet.PivotTables
        mySheet.Range(myPivot.TableRange2.Address).Delete Shift:=xlUp
    Next myPivot
Next mySheet
End Sub

This code will remove pivot tables from all the worksheets from a workbook.

How this Code Works

Here’s a line-by-line explanation of the above code:

• Dim mySheet As Worksheet and Dim myPivot As PivotTable: These lines declare two variables. mySheet is an object variable that represents each worksheet in the workbook, and myPivot is an object variable representing each pivot table on a worksheet.
• On Error Resume Next: This line allows the code to continue running even if it encounters an error. This is helpful because it prevents the code from stopping if it doesn’t find a pivot table on a worksheet.
• For Each mySheet In ActiveWorkbook.Worksheets: This line starts a loop that will go through all the worksheets in the active workbook.
• For Each myPivot In mySheet.PivotTables: This line starts a nested loop through each pivot table on the current worksheet.
• mySheet.Range(myPivot.TableRange2.Address).Delete Shift:=xlUpThis line deletes the entire range of cells occupied by the current pivot table. The Shift:=xlUp part causes the cells below the deleted range to shift up to replace the deleted cells, preventing blank rows from being left in the worksheet.

Points to Take Care Before You Delete a Pivot Table

Before you delete a pivot table, it’s important to check that:

• Verify that you have selected the correct pivot table.
• Remember that deleting a pivot table does not delete the original data. However, changes in the pivot table, like calculated fields and items, will be lost.
• Ensure that no other data or formulas in your worksheet is linked or connected to the pivot table you want to delete. Deleting the pivot table may cause errors in these cases.
• Consider copying the pivot table and pasting it as values in another location to preserve the table format and analysis without keeping the pivot functionalities.