Excel users often tend to look for tools that can easily crunch their data consisting of thousands of rows and columns. Though it would be a lengthy process you can consider creating pivot tables. Before learning how to delete a pivot table, you must understand how you can create a Pivot Table, that’s why you must spend some time on it.
Pivot Table is known to be an influential tool used in Excel that gives you the option to communicate through data stored in a spreadsheet so that you can evaluate data in different ways. A Pivot table can be deleted with just a single key press.
Here, you will get to know how to delete a pivot table in Excel. Below are some of the methods you can use to delete a Pivot Table:
- How to Delete a Pivot Table Along with the Data
- How to Delete a Pivot Table and Keep the Data
- How to Delete a Pivot Table and Keep the Main Pivot Table
- How to Delete All Pivot Tables in a Workbook
How to Delete a Pivot Table and Keep the Data?
Below is the dataset you can follow:
You can delete a pivot table with a single keystroke but it is important to know how to keep the data when you need to delete a Pivot Table. Just copy the entire table and paste it somewhere else as values.
For this, follow the steps given below:
- Click on a cell from the Pivot Table.
- Click on the “Analyze” tab given in the ribbon.
- Press the Select option and click on the Entire Pivot Table.
- Press CTRL + C and copy the Pivot Table. Open a favorable location where you can keep this data.
- Press ALT + E + S + V and paste the data as values.
- Get back to Pivot Table and choose all the values by pressing CTRL + A and then the delete button.
How to Keep a Pivot Table and Delete the Data?
Sometimes, you don’t need the data in a Pivot Table so instead of deleting the whole table you can just delete the data while keeping the Table with you. For this, follow the steps given below:
- Choose any cell of the Pivot Table.
- Click on the “Analyze” button from the ribbon.
- Click on the Clear option and choose Clear All.
- Now, you will notice the entire data is removed from the table whereas the table is still there.
How to Delete Pivot Table Along with Data?
Well, this one is the easiest thing of all. Just delete the sheet containing the table when there is no other data in the sheet. Or else, choose a cell in the table and press CTRL + A to select the whole Pivot Table. Click on the delete option. Using this method will not select the entire Pivot Table when filters are applied.
Below are some easy-to-follow steps you can use:
- Choose a cell in Pivot Table.
- Click on the “Analyze” option from the ribbon.
- Choose the “Select” option and click on Entire Pivot Table.
- Now, you will notice the whole Pivot Table including its data is selected. Press the delete key.
How to Delete All Pivot Tables in the Entire Workbook?
Sometimes, you may find manually deleting tables a boring method and if you have many tables to delete then it becomes a real pain. Using VBA Code is a reliable method to deal with such situations.
Follow the steps given below:
- Press ALT + F11 and open the VBA editor.
- Under the Projects section, on the left-hand side, find the workbook and right-click on any sheets.
- Now, click on the “Insert” option and choose Module.
You will see a Module window where you need to paste the following code:
Sub ClearPivotTables()
Dim Ws As Worksheet, Pt As PivotTable
On Error Resume Next
For Each Ws In ActiveWorkbook.Worksheet
For Each Pt In Ws.PivotTables
Pt.TableRange2.Clear
Next Pt
Next Ws
End Sub
The VBA code is self-explanatory. First of all, go to the Worksheets as Ws and PivotTable as Pt. Then “For” loop is used to tell Excel to clear the table range for each pivot table in all worksheets given in the workbook.
- Press the Run button given in the VBA editor.
- Now you will notice Excel has deleted all the Pivot Tables in the worksheet by default.
To Sum Up
The above-mentioned methods are highly useful because you can save time by applying these tricks. Keep trying new methods and continue exploring Excel.
