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 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 delete the entire pivot table but retain the resulting data, the procedure is more or less the same

Steps to delete the Pivot table but Keep the Resulting Data:

Select any cell in 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.

We have learned the easy way of removing or deleting the pivot table in Excel. But deleting many pivot tables in a workbook is not that easy. So we need VBA code to delete pivot tables in one go

Below is VBA code:

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 you 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.

Things to Remember

We can remove the Excel pivot table and pivot worksheet as well.
Once the Excel pivot table is removed by using VBA code we cannot undo the action, so it is safe to have a backup copy.
Once the Excel pivot table is removed any changes in the database will not reflect on the removed field.

Article Tags :

Computer Subject

Excel