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: 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).
Below are the steps to delete the Pivot table as well as any summary data: The above steps would delete the Pivot Table. 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: The above steps would delete the Pivot Table but still keep the resulting data.
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.
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:
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. Below is the VBA code that will delete all Pivot Tables in one go: 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: 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:
Filter Data in a Pivot Table in Excel Group Dates in Pivot Tables in Excel Group Numbers in Pivot Table in Excel How to Apply Conditional Formatting in a Pivot Table in Excel Using Slicers in Excel Pivot Table Pivot Table Calculated Field How to Refresh Pivot Table in Excel Move Pivot Table to Different Worksheet or Workbook
Did it for me, now all tables are deleted.
