How to Delete a Pivot Table4 Different Ways!

Written By:
Lisa Borga
Reviewed By:
FundsNet Staff

A pivot table is a very useful tool in Excel that allows users to rearrange the data in a spreadsheet so as to analyze it in different ways.

But, if you have too many pivot tables or you have a particularly large data set, these tables can cause the performance of your workbook to decline due to the fact Pivot tables use a considerable amount of memory, processing power, and file space.

Because of this, it is generally a good idea to delete them.

So, we will explain a few different ways to delete Pivot tables in this article.

We will use the Pivot table below in our examples.

How To Delete a Pivot Table While Keeping Resulting Data

In certain cases, you may not want to remove a Pivot table from your Excel spreadsheet while retaining the data.

This is common when you have made use of a pivot table to calculate, summarize, and analyze data yourself and just want to share the resulting numbers with others.

In other cases, a pivot table may just bloat the file size or distract from the focus of your spreadsheet.

No matter the case, it is possible to remove a pivot table while keeping the data.

Here is how:

  1. Select a cell in the pivot table. This will make the “Analyze” tab appear in the ribbon menu, which you can then click on.
  2. In the “Actions” group, click on the “Select” option, and on the resulting drop-down list, select “Entire Pivot Table.”
  3. Right-click anywhere on the Pivot Table and select “Copy” in order to copy all of the data from the table.
  4. Select any cell in the worksheet where you would like the data from the worksheet to be placed or in another worksheet altogether.
  5. Navigate to the “Home” table and select the “Paste” button.
  6. On the drop-down list, look to the “Paste Values” group and select the “Paste as Value” option.
  7. The data will now be pasted without the Pivot Table. You can now select the entire Pivot Table again and delete it by using the “Delete” key on your keyboard or through the “Clear All” command.

How To Delete a Pivot Table and Its Data

There are a number of ways to delete a Pivot table with its data.

One of these ways is to delete the entire sheet that contains the Pivot table as long as you don’t have other data on it.

You could also delete the Pivot table by clicking on a cell in the table and selecting the whole table, then deleting it by using CTRL + A.

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

The following method will work whether or not you have applied filters to the Pivot table.

  • Click on one of the cells in the Pivot table.
  • Find “PivotTable Tools” and click the “PivotTable Analyze” tab located under it.
  • Then, locate the “Actions” group and find the “Entire PivotTable” button and select it.
  • Next, press delete on your keyboard.

Once you complete these steps, the Pivot table, along with its data, should be deleted from your worksheet.

How To Delete All of the Pivot Tables From Your Workbook

If you decide you want to delete all of the Pivot tables from a worksheet, you could remove them one at a time.

But this would be rather time-consuming.

So, instead, you could use VBA script to delete them, and the code for this is listed below.

Sub ClearPivotTables()

For Each ws In ActiveWorkbook.Worksheets

For Each PvtTable In ws.PivotTables

PvtTable.TableRange2.Clear

Next PvtTable

Next ws

End Sub

Here are the steps to follow when using this function.

  • Go to the “Developer” menu and find “Visual Basic.” Then, open the VBA window.
  • Next, click on Insert and then choose module.
  • Then, copy the script listed above and copy it into the module scripting window once it opens.
  • Once you’ve completed the above steps, you can press the key F5 to run the code.

Performing these above steps should delete all of your Pivot tables.

How To Delete the Data From a Pivot Table While Keeping the Table

There might be a situation in which you want to delete the data from a Pivot table while keeping the Pivot table itself.

You can do this by carrying out the steps below.

  • Click on one of the steps in your Pivot table.
  • Go to “PivotTable Tools” and click the “PivotTable Analyze” tab.
  • Go to the “Actions” group and then to “Select” and choose the button “Entire PivotTable.”
  • Your Pivot table should now be selected.
  • Now, from the “Actions” group, choose” Clear” and then “Clear All.”

The above steps should clear your data but retain your Pivot table.

Final Thoughts

This article explained four different ways to delete Pivot tables or the data in them.

These methods should help make it easier to remove the data from your Pivot table when you want to use the table for something different or delete tables you no longer need, thus improving the performance of your workbook.