How to Delete a Pivot Table in Excel? 4 Best Methods
Note: This tutorial on how to delete a pivot table in Excel is suitable for all Excel versions including Office 365.
If you are a regular Excel user, you may already know that the Pivot table is one of the most versatile Excel features. It allows you to slice and dice your data as per your needs and helps you to draw quick insights from the dataset.
But, more often than not, Pivot tables tend to encumber an Excel workbook’s performance. This happens especially when they are overused or if the data set is huge.
Related:
How to Extract an Excel Substring? – 6 Best Methods
How to Superscript in Excel? (9 Best Methods)
How To Find Duplicates in Excel? (3 Easy Methods)
The main reason behind this is that Pivot tables increase the Excel file size considerably and consume more memory and processing power.
Hence, it is advisable to delete Pivot tables after using them. But, it is not as simple as it sounds.
In this tutorial, I’ll show you how to delete a Pivot table in Excel without any hiccups.
How to Delete a Pivot Table? 4 Approaches
Depending on your requirements, you may need to use one of the following four approaches in your workbook.
- How to Delete a Pivot Table but Keep the Data?
- Keep the Pivot Table but Delete the Data
- Delete the Pivot Table Along with its Data
- Delete all Pivot Tables in the Entire Workbook.
I’ll explain each one of these methods with detailed examples in the following section.
How to Delete a Pivot Table but Keep the Data?
This is one of the frequently asked questions about Pivot tables. How to delete a pivot table but keep its values?
All you have to do is just copy the entire Pivot table and paste it as values in some other suitable location.
Follow these steps to do this easily:
Step 1
Click on any Pivot Table cell.
Step 2
An “Analyze” tab will appear in the ribbon as soon as you do this. Click on it.
Step 3
Then under it, click on the Select option and choose Entire PivotTable.
Step 4
Press Ctrl+C to copy the Pivot Table and go to a suitable location where you need to place the Pivot Table data.
Step 5
Press Alt+E+S+V to paste the data as values.
Step 6
Go back to the Pivot Table, select all values using Ctrl+A and hit the delete button.
Keep the Pivot Table but Delete the Data
This is another common requirement that users face. Sometimes, you need to just clear the Pivot Table and start again with a fresh summary without deleting it.
To do this, follow these steps:
- Click on any Pivot Table cell.
- An “Analyze” tab will appear in the ribbon as soon as you do this. Click on it.
- Then under it, click on the Clear button and choose Clear All.
Now, the Pivot table will remain, but its data will be cleared.
Also Read:
How to Use Goal Seek in Excel? (3 Simple Examples)
How to Insert Multiple Rows in Excel? The 4 Best Methods
How to Autofit Excel Cells? 3 Best Methods
Delete the Pivot Table Along with its Data
This is a pretty straightforward requirement.
The best way to do this is to just delete the Sheet that contains the Pivot table, provided if there is no other data in that particular sheet.
Or else, click on any cell inside the Pivot Table and press Ctrl+A to select the entire Pivot table and hit delete. This method will not select the entire Pivot Table if filters are applied.
To avoid this, the foolproof method is to do the following:
- Click on any Pivot Table cell.
- An “Analyze” tab will appear in the ribbon as soon as you do this. Click on it.
- Then under it, click on the Select option and choose Entire PivotTable.
- Now, your entire Pivot Table is selected including filtered data if any. Hit the delete key.
Delete all Pivot Tables in the Entire Workbook
This process of manually deleting Pivot tables can become very tiresome, especially if you have a lot of them in your workbook.
There is a simpler way to handle this by running a VBA code.
Don’t worry, it is very easy to do. Just follow these simple steps:
- Hit Alt+F11 to open the VBA editor.
- In the left-hand pane under Projects, locate your workbook and right-click on any one of the sheets under it.
- Then click on Insert and choose Module.
- A module window will now appear. Paste the following code there.
Sub ClearPivotTables()
Dim Ws As Worksheet, Pt As PivotTable
On Error Resume Next
For Each Ws In ActiveWorkbook.Worksheets
For Each Pt In Ws.PivotTables
Pt.TableRange2.Clear
Next Pt
Next Ws
End Sub
The VBA code is self-explanatory. First, we refer to Worksheets as Ws and PivotTable as Pt. Then, we use a “For” loop to instruct Excel to clear the table range for each pivot table in every worksheet inside the workbook.
- Now hit the Run button in the VBA editor toolbar.
Excel will now delete all Pivot Tables in your worksheet automatically.
Suggested Reads:
How to Group Worksheets in Excel? (In 3 Simple Steps)
How to Shade Every Other Row in Excel? (5 Best Methods)
How to Use the Excel Fill Handle Easily? (Top 3 Uses with Examples)
FAQs
How to delete the Pivot Table cache?
You can delete the Pivot Table cache or share it with multiple pivot tables to reduce calculation times and improve performance. To do this, select the Pivot Table you want to share or delete. Then, go to the Home tab and click on Clear-All. This will clear the cache of the Pivot-Table along with its calculations. Now, you can duplicate it and share the same cache between these two Pivot Tables.
How to delete a calculated field in Pivot Table?
To delete a calculated field in Pivot Table, click anywhere inside it. On the “Pivot Table Analyze” tab click on the Fields, Items & Sets option. Now, click on “Calculated Field”. In the “Name” box choose the field you want to delete and click Delete.
Let’s Wrap Up
That’s all folks. These are the different methods to delete Pivot tables in Excel. Hereafter, use these techniques for a hassle-free Excel experience. If you have any questions regarding this or any other Excel feature, please let us know in the comments. We are always happy to help.
If you need more high-quality Excel guides, please check out our free Excel resources centre.
Ready to dive deep into Excel? Simon Sez IT has been teaching Excel for over ten years. For a low, monthly fee you can get access to 100+ IT training courses. Click here for advanced Excel courses with in-depth training modules.
Simon Calder
Chris “Simon” Calder was working as a Project Manager in IT for one of Los Angeles’ most prestigious cultural institutions, LACMA.He taught himself to use Microsoft Project from a giant textbook and hated every moment of it. Online learning was in its infancy then, but he spotted an opportunity and made an online MS Project course - the rest, as they say, is history!
