Whether you do not need a Pivot Table or want to get rid of errors like “Cannot change this part of a PivotTable report,” it’s a good idea to delete them immediately and declutter your sheet.
Whatever your reasons are, you just have to select the Columns with the Pivot Table range. Right-click on the selected data and choose Delete.
However, the above method is only useful when the remaining Columns/Rows are empty. If the selected cells are populated with other data, Excel deletes them too.
In cases like these, you could use other ways to delete a Pivot Table.
Delete a Sheet
The quickest way to get rid of your Pivot Table is by deleting a sheet. You can use this method when you have only Pivoted data in a new worksheet.
For that, right-click on the Sheet Tab and pick Delete. Both the Sheet and Table will be gone instantly.
From the PivotTable Analyze Tab
If you want to delete only the Pivot Table from your data sheet, click any Cell within the table.
Head to the Pivot Table Analyze Tab. From the Actions group, choose Select > Entire Pivot Table. Then, press the DEL key.
It will remove the table permanently.
While you can select the Pivot Table using the mouse and delete it, it fails to select the filtered items. That is why we’re using the default button from the PivotTable Analyze Tab to select.
Users who deleted their Source Worksheet of the Pivot Table can also opt for this method.
Copy-Paste
Next, we will copy-paste the Pivot Table to delete it. Yes! Copy-paste it.
This approach is especially dedicated to users who want to remove the Pivot Table but retain the outcomes. In short, we will just get rid of the table format which also helps to minimize the large Excel File.
Suppose, I have a Pivot table from cell range A3:B8.
Firstly, select Pivot Table using the mouse. If you have Filters, I suggest you select from Pivot Table Analyze. Press Ctrl + C to copy it.
Then, from the Home Tab, expand Paste and pick Values.
Use Clear All Option
Some users might want to keep the Pivot Table but clear the fields from it. In that case, there are two things you could do.
First, untick all options in the Field Lists.
Or, select Pivot Table. From the Pivot Table Analyze tab, click Clear > Clear All.
Use VBA
If you’d rather wish to delete the entire existing Pivot Tables at once, run the VBA code I have constructed below. Once you save the Macro, you can just run it the next time you need to remove it.
Honestly, I’m an avid VBA user myself as I can save a lot of time and enhance my productivity during work.
Although VBAs and Macros lie on the advanced side of Excel, the below code is beginner-friendly.
Having said that, once you start using Macros, you’ll get a hang of it soon. It’s by far the best way to do monotonous tasks.
Firstly, right-click on Sheet Name and pick View Code.
Then, copy the code mentioned in the box. Paste it into the VBA window.
Sub Delete_Entire_PivotTable()
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
Press the F5 key to run.
Unpivot Table
Another way to remove the Pivot Table is by Unpivoting the data. Now, this technique is different from all the methods we’ve covered above because we won’t actually delete the table.
You can use this when you want your Pivot Table to return back to a normal Table like the source. It’s also a savior when you’ve accidentally deleted the source sheet.
If you want to do this, here’s our step-by-step guide article on How to Unpivot Data.