How to Delete a Pivot Table in Excel?

by

Pivot tables are great analytical tools that help you sort, organize and summarize data.

However, they can hamper the performance of your workbook if overused, or if you have a very large dataset.

This is because Pivot tables take up a lot of file space, memory and processing power.

As such, it is good practice to delete Pivot tables when you’re done using them.

In this tutorial we will show you how to delete a Pivot table in 4 cases:

  • When you want to delete the entire table along with the data
  • When you want to delete the Pivot table but keep the data
  • When you want to delete the Pivot table data, but keep the main Pivot table
  • When you want to delete all Pivot tables in a workbook

In case you want to keep the Pivot table and the data, but just want to clear out the Pivot table cache (to make more space or improve performance), we have also included a section to help you do that. 

To demonstrate each method we are going to use the following sample Pivot table:

Pivot Table data

How to Delete the Entire Pivot Table along with the Data

If you want to wipe out the entire Pivot table along with all the data in it, there are plenty of ways.

One way is to simply delete the sheet containing the Pivot table, provided there is no other data on the sheet.

Another way is to click on any cell in the Pivot table, select the entire table by pressing CTRL+A, and then press the Delete button from the keyboard.

However, if you have filters applied to the Pivot table, then this method will not work.

The best way to remove the entire Pivot table, though, is as follows:

  1. Click on any cell on the pivot table.
  2. Click on the PivotTable Analyze tab under PivotTable Tools.
Click on Pivot Table Analyze tab
  1. From the Actions group, navigate to Select->Entire PivotTable button.
Select Entire Pivot Table
  1. This should select your entire Pivot table (including any filtered data).
Entire Pivot Table is selected
  1. Press the ‘Delete’ key from your keyboard.

Your entire Pivot table, along with any data in it should now get deleted from your worksheet.

Also read: How to Connect Slicer to Multiple Pivot Tables

How to Delete a Pivot Table but Keep the Data

Some people like to use Pivot tables as an intermediary step, to obtain a certain result.

So, instead of deleting the entire pivot table along with all data in it, you might want to preserve the data. In such cases, you could simply copy the data you need and paste it as value somewhere else.

Here’s a step-by-step on how to do this:

  1. Click on any cell in the pivot table.
  2. Click on the PivotTable Analyze tab under PivotTable Tools
  3. From the Actions group, navigate to Select->Entire PivotTable button.
  4. This should select your entire Pivot table.
  5. Right-click on the highlighted table and select Copy. Alternatively you can use the CTRL+C shortcut.
Right-click on Pivot Table and click Copy
  1. Right click on the first cell of the new range where you want your pivot table data pasted.
  2. Select Paste->Paste Values.
Paste as values
  1. Now highlight the original pivot table again and press the Delete key from your keyboard.

How to Delete a Pivot Table Data but Keep the Pivot Table

In some cases, you need to just clear out the data from the main pivot table, to make room for a fresh summary, without having to delete the entire Pivot table altogether.

To do this, follow the steps below:

  1. Click on any cell in the pivot table.
  2. Click on the PivotTable Analyze tab under PivotTable Tools
  3. From the Actions group, navigate to Select->Entire PivotTable button.
  4. This should select your entire Pivot table.
  5. From the Actions group, navigate to Clear->Clear All.
Select Clear All

This will clear out your data, without deleting the pivot table.

Removed data without deleting the Pivot table

How to Delete All Pivot Tables from a Workbook (VBA)

If you have a large number of pivot tables in your workbook and need to get rid of all of them, doing it manually can get tiresome.

There’s an easy way to do this, and for that, you will need to use a VBA script.

Here’s the code that you can use:

Sub ClearPivotTables()
For Each ws In ActiveWorkbook.Worksheets
For Each PvtTable In ws.PivotTables
PvtTable.TableRange2.Clear
Next PvtTable
Next ws
End Sub

To get the above function to work, follow the steps listed below:

  1. Navigate to Visual Basic from the Developer menu. This will open the VBA window.
Click on Visual Basic
  1. Click on Insert->Module.
Insert a new module
  1. Copy the above script into the module scripting window that opens.
Copy-paste code in the module
  1. You’re now all set. Whenever you want to run the code, simply click on the ‘Run Sub/User Form’ button or press the F5 key.
Run the macro code

All the pivot tables in your workbook should now get deleted.

Explanation of the Code

The above code is quite simple.

It uses a For Next loop to go through each worksheet in the active workbook, and another nested loop to go through each Pivot table in every worksheet.

For every Pivot table in a worksheet, it uses the Clear method to clear out the range of cells that contain the Pivot table.

Clearing The Pivot table Cache

Whenever you create a Pivot table in Excel it stores a copy of your source data in a ‘Pivot table cache’ – an area occupying space in Excel’s memory.

When you adjust the Pivot table, it refers to this Pivot cache, instead of the actual source data.

This helps ensure quick responses to changes made to your Pivot tables.

However, this also means your file is using up double the amount of memory and resources.

So, if you have multiple Pivot tables, imagine the size of your file and how much processing time it can take!

In such cases, it’s a good idea to delete the Pivot table cache when you’re done using it, before moving on to the next Pivot table.

This way, you can recycle the space occupied by the cache to be used for multiple Pivot tables, thereby reducing calculation time and improving performance.

To clear the Pivot table cache, follow the steps listed below:

  1. Select the Pivot table.
  2. From the Home tab, click on Clear->Clear All.
  3. This will clear the Pivot table cache, leaving you space for the next Pivot table.

In this tutorial, we showed you how to delete a pivot table in 4 different scenarios.

We also showed you how you can clear the Pivot table cache to ensure better performance when using multiple Pivot tables in a workbook.

We hope these techniques help you easily and quickly delete pivot tables, making space for more analytical adventures.

Other Excel tutorials you may also like:

I am a huge fan of Microsoft Excel and love sharing my knowledge through articles and tutorials. I work as a business analyst and use Microsoft Excel extensively in my daily tasks. My aim is to help you unleash the full potential of Excel and become a data-slaying wizard yourself.

Leave a Comment