How to Delete a Pivot Table in Excel – With and Without Data
Pivot Table is the show-stopper data crunching tool of Excel. If you have a huge (really huge) dataset ahead of you, a simple Pivot Table will help you make an interactive summary out of it 🚀
You can then readily drag and drop fields as rows, columns, and values to change the dimensions of your summary.
However, once you have made the designated pivot table out of your data – how can you delete it? You can delete the whole pivot table, only the pivot table (not the data inside it), or only the data and not the pivot table 💪
And this guide will teach you how to do all three of them. So get your hands on our free sample workbook here and dive in straight.
Table of Contents
Delete the pivot table including the data
The image below shows a very smart Pivot table in Excel that summarizes a messy dataset🧹
Very Nice! Let’s now delete it 😝
Deleting Pivot Tables in Excel is not as straightforward as simply selecting the table and hitting the backspace key.
If you try to delete any data from an Excel Pivot Table, here’s the error that Excel throws your way.
To delete the Pivot table from your sheet in Excel, follow these steps:
- Select any cell of the Pivot Table.
You’d see two new tabs on the Ribbon: the PivotTable Analyze tab and the Design tab 👇
- Go to the PivotTable Analyze tab > Select > Entire PivotTable.
The entire Pivot Table will now be selected.
- Hit the Backspace key or the Delete key.
And whoosh! The entire pivot table (including the data) disappears from the sheet 🤩
Pro Tip!
The key to deleting a PivotTable along with the data is to select the whole PivotTable before you delete it. This can also be done by:
- Pressing the Control key + A to select the entire sheet and then hitting the Delete button.
- Select the columns or rows that contain the Pivot Table and delete them.
- Or by deleting the entire sheet from the Sheet Tab at the bottom.
However, all of these methods run the risk of losing all other data of the relevant sheet (or rows/columns) too 😲
Delete the pivot table but keep the data
We are again going to delete the Pivot Table from our sheet. But this time it’s only going to be the PivotTable and not the data inside it 🙈
You might want to do so when you initially made a Pivot Table to summarize your data.
And after you’ve got the desired summary look of your data, you want to keep that data only and not the Pivot Table running at the backend 💡
Kasper Langmann, Microsoft Office SpecialistDeleting the PivotTable only and not the data inside is utterly doable. Check this out:
- Select any cell of your Pivot Table.
- Press the Control key + A.
The whole Pivot Table will be selected.
- Press the Control key + C to copy the Pivot table.
- Activate the cell where you want to retain the data of the Pivot Table.
We are selecting Cell A1. Yes, it’s fine even to paste it in place of the Pivot table.
Doing so will replace the Pivot table with simple data.
- Go to the Home Tab > Paste > Paste Values.
Alternatively, you may use the shortcut key (Alt key > E > S > V) to paste values only 💻
Kasper Langmann, Microsoft Office SpecialistAnd that’s it, the Pivot table is replaced with the Pivot table data only.
Pro Tip!
As Excel only pastes values, all the formatting (the font size, color, number format, etc.) will be removed from the data.
You’ll have to reapply it if need be 🎨
Delete the data result but keep the pivot table
The last way of deleting a Pivot Table is to delete the data from a Pivot table but retain the Pivot Table.
Let’s do this here. To delete the data from the Pivot Table in our example (same as above), follow these steps ✍
- Select any cell of your Pivot Table.
- Go to the PivotTable Analyze tab > Clear > Clear All.
And swish! Everything’s gone. We no more have the data but only the Pivot Table structure.
Click on it, and the PivotTable fields will launch to the right of your sheet 💁♀️
You can expect to exercise this method when you want to build a Pivot Table all over again.
The Pivot table source data remains the same, you can rearrange it as the fields of the Pivot Table🚴♂️
Kasper Langmann, Microsoft Office SpecialistThat’s it – Now what?
The guide above discusses all three possible ways how you may delete a pivot table in Excel. With this, you can now tweak your Excel datasets any way you like.
But hold on! Pivot table, though very powerful, is only one feature of Microsoft Excel. Excel has got so many more amazing features, tools, and functions for you to unfold 🎯
Some of the top functions of Excel – are the VLOOKUP, SUMIF, and IF functions.
Start learning them by enrolling in my 30-minute free email course right now.
Other resources
To delete pivot tables, you must first know how to create Pivot Tables in an Excel workbook, no?
Hop on here to read our blog on how to create Pivot Tables in Excel now.