How to Delete a Pivot Table in Excel (Easy Step-by-Step Guide)

Pivot Table is one of the best features in Excel.

You don’t need to know any formula or coding to quickly crunch thousands of rows of data and create quick summaries out of it. All you need to know is how to drag and drop and a little bit of knowledge of how to create a Pivot Table.

In this tutorial, I will show you various ways to delete a Pivot Table from Excel.

How to Delete a Pivot Table in Excel

Since you’re reading a tutorial about removing or deleting a Pivot Table, I am assuming you already have a Pivot Table(s) in place that you want to remove.

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.

Below are some scenarios that I will be covering in this tutorial:

  1. Delete the Pivot Table & the resulting data (the summary created using the Pivot Table)
  2. Delete the Pivot Table but keep the resulting data
  3. Delete the resulting data but keep the Pivot Table
  4. Delete all the Pivot Tables in one go

Let’s dive in and see each of these methods.

Delete the Pivot Table and the Resulting Data

Below is an example where I have created a Pivot table and used it to get the Sum of Revenue for different regions (to which I will be referring to as Pivot Table summary data in this tutorial).

Delete Pivot Table Summary Data

Below are the steps to delete the Pivot table as well as any summary data:

  1. Select any cell in the Pivot Table
  2. Click on the ‘Analyze’ tab in the ribbon. This is a contextual tab that appears only when you have selected any cell in the Pivot Table.Click the Analyze Tab
  3. In the Actions group, click on the ‘Select’ option.Click on Select Option
  4. Click on Entire Pivot table. This will select the entire Pivot table.Click on Entire Pivot Table
  5. Hit the Delete key.

The above steps would delete the Pivot Table.

Note that you can also select the entire Pivot Table by selecting any cell and then using the keyboard shortcut ‘Control + A‘. In case you have filters applied in the Pivot table, Control A will not select the entire Pivot Table. You need to use the method shown above (or select the Pivot Table manually)

Another quick way to delete the Pivot Table is to simply delete the worksheet that has the Pivot Table. Of course, you wouldn’t want to do this if you have any other data in the worksheet.

Delete the Pivot Table but Keep the Resulting Data

There may be cases when you want to delete the Pivot table, but not the resulting data that you have got after using the Pivot table. This could be the case when you have used the Pivot Table and then want to send to your manager/client only the resulting data.

Another case where this may be needed is when your Pivot Table is too heavy and is bloating your worksheet. Deleting such a Pivot table can drastically reduce the Excel file size.

For example, in the below example, I want to remove the Pivot Table, but I still want to keep the data in cell A3:B8

Below are the steps to do this:

  1. Select any cell in the Pivot Table
  2. Click on the ‘Analyze’ tab in the ribbon. This is a contextual tab that appears only when you have selected any cell in the Pivot Table.Click the Analyze Tab
  3. In the Actions group, click on the ‘Select’ option.Click on Select Option
  4. Click on Entire Pivot table. This will select the entire Pivot table.Click on Entire Pivot Table
  5. Right-click on any cell of the selected Pivot Table.
  6. Click on Copy. This will copy the data of the entire Pivot Table.Copy the Selected Pivot table
  7. Click the Home tab.Click the Home tab
  8. Click on the Paste optionClick on Paste Option
  9. In the Paste Values section, click on the first icon (which is of Paste as Value).Paste as Value Over the Pivot Table

The above steps would delete the Pivot Table but still keep the resulting data.

Resulting Data after Pivot Table is removed

Some Keyboard Shortcuts you can use:

  • Select any cell in the Pivot Table and use the Keyboard ‘Control + A’ to select the entire Pivot Table
  • Once you have selected the entire Pivot table and copied the data, you can use the following keyboard shortcut to paste as values – ALT+E+S+V+Enter (one key after the other)

You can also use the same steps shown above to copy the data from the Pivot Table and pasting it as values at some other location (somewhere in the same worksheet or some other worksheet/workbook).  Once you have the data, you can then delete the Pivot Table.

Note: When you copy data (Control C) from a Pivot Table and paste it (Control V) anywhere in the worksheet, it simply creates another Pivot Table. If you want to keep the resulting data only, and not the Pivot Table, make sure you paste as values.

Delete the Resulting Data but Keep the Pivot Table

Suppose you have created a Pivot Table and summarized the data (using filter and columns/rows headers to get the data you want) as shown below.

If you want to only remove the data that you have got while keeping the Pivot Table (so that you can rearrange and create another summary), you can do that as well.

If you select the data and hit the delete key, it will delete the Pivot Table as well.

Below are the steps to keep the Pivot table and remove the resulting data only:

  1. Select any cell in the Pivot Table
  2. Click on the ‘Analyze’ tab in the ribbon. This is a contextual tab that appears only when you have selected any cell in the Pivot Table.Click the Analyze Tab
  3. In the Actions group, click on ‘Clear’ option.Clear Option in Analyze Tab
  4. Click on the ‘Clear All’ option.Clear All Pivot Table

Delete All Pivot Tables in One Go

In case you have multiple Pivot tables and you want to delete all of these at one go, you can do that using a simple VBA code.

Caution: Once you delete these Pivot Tables with the VBA code, you’ll not be able to get these back. So be absolutely sure when you do this, and if needed, create a backup copy to avoid losing these.

Below is the VBA code that will delete all Pivot Tables in one go:

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 above coded uses the For-Each-Next loop to go through each worksheet in the active workbook. In each worksheet, it checks if there is a Pivot table or not and deletes it (if there is).

Where to Put this VBA code?

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:

  1. Open a workbook from which you want to remove the Pivot Tables.
  2. Use the shortcut ALT + F11 (this opens the VBA Editor window).
  3. In this VBA Editor window, on the left, there is a project explorer (where all the workbooks and worksheets are listed). Right-click on any object in the workbook where you want this code to work.Right-click on any VBA object
  4. Hover the cursor on Insert.Hover the Cursor on Insert
  5. Click on Module. This will insert a new module for the current workbook.Click on Module
  6. In the module window (that will appear on the right), copy and paste the above code.Copy and Paste the VBA Code in the module window
  7. Now you can run this VBA code by clicking on the green button in the VBA Editor toolbar.Run the Macro using the play button in menu toolbar

Here is an article where I cover different ways to run a VBA macro in Excel.

As soon as you run this code, it will remove all the Pivot tables from all the worksheets in the workbook.

You May Also like the following articles:

>