OmniDocHow to Delete a Pivot Table and Keep Your Data
Learn how to cancel a pivot table in Excel without losing your source data or copied values, plus the checks to run afterward.
Many people search for how to cancel a pivot table in Excel because the summary no longer answers the question they have, or because the layout is cluttering a sheet they still need. The worry is usually the same: deleting the table might wipe out the numbers underneath it. It won't, as long as you remove the pivot table itself and not the data it was built from.
This guide shows you how to delete a pivot table in an .xlsx workbook while keeping the source data, any values you have copied out of it, and the rest of the file intact. It also covers the cases that cause trouble afterward, such as broken formulas and leftover charts.
How do you cancel a pivot table without losing data? The short answer
Select every cell the pivot table occupies and clear them, and the summary disappears while your source rows stay where they were. Copy any results you want to keep as values before you clear anything.
| Your situation | What to do | Why |
|---|---|---|
| You only want the summary gone | Select the whole pivot table range and press Delete | Removes the pivot layout; the source data is on a separate range and is untouched |
| You want to keep the numbers it showed | Copy the table, paste as values somewhere else, then delete the original | Values stay as plain numbers, so they survive once the pivot is gone |
| The sheet looks wrong after deleting | Clear formatting on the leftover cells, separately | Formatting and the pivot table are different things; clearing one does not remove the other |
| A chart or slicer still references the table | Delete the chart or slicer, or re-point it to a source range | Pivot charts and slicers depend on the pivot table and can break or go blank |
Before you start
Check these items before you delete anything. Each one takes a minute and prevents a common mistakes.
- Save a copy of the file. Deleting a pivot table is easy to undo right after you do it, but after you save and close, the layout is gone for good. A copy gives you a fallback.
- Find the source data. Click any cell in the pivot table and look at its source range. The source is usually a table or range on another sheet. Deleting the pivot table does not touch it, but you need to know where it is so you can confirm it survived.
- Look for dependent objects. Pivot charts, slicers, and formulas that use GETPIVOTDATA all rely on the pivot table. Note where they are so you can clean them up afterward.
- Decide what you need to keep. If you want the totals or group counts, copy them as values now. Once the pivot table is removed, the formula-driven summary cannot be recovered without rebuilding it.
- Confirm the pivot table is not shared. If another sheet or external file links to cells in the pivot, deleting it will break those links. Search for references to the sheet name before you remove anything.
Deleting a pivot table in Excel, step by step
- Open the workbook and go to the sheet with the pivot table. Pivot tables live on the sheet where they were created, so start there rather than on the source data sheet.
- Click any cell inside the pivot table. This tells Excel which object you mean. Selecting a single cell first makes it easy to find the table's full extent.
- Select the entire pivot table range. Drag across every cell the summary covers, including row labels, column headers, and grand totals. Partial selections leave fragments behind.
- Copy values first if you need the results. Copy the selected range, then use paste special to paste values into a blank area or a new sheet. This keeps the numbers without the pivot link.
- Clear the pivot table cells. With the range still selected, press the Delete key. The pivot layout is removed, and the cells go blank.
- Confirm the source data is still there. Go to the sheet that holds the original rows. If they are intact, the deletion did what you wanted.
- Remove or update dependent objects. Delete any pivot charts or slicers you no longer need, and re-point any charts that should use the source data directly.
- Save the workbook under the same name or a new one. Saving keeps your cleanup in place. Use a new name if you want to keep the earlier version with the pivot table.
Check the result
After you delete the pivot table, check these things. The table below lists what to look for and what to do if something is off.
| Check | How to see it | If it's wrong |
|---|---|---|
| Source data is intact | Scroll through the sheet where the original rows live and compare a few values | If rows are missing, press Undo right away or restore from your saved copy |
| Copied values are plain numbers | Click a copied cell and look at the formula bar; it should show a number, not a pivot reference | If it still shows a pivot function, repeat the copy and paste as values step |
| No broken formulas | Search the workbook for #REF! and check formulas that mentioned the pivot table name | Rewrite those formulas to point at the source range or the pasted values |
| No orphaned charts or slicers | Look on every sheet for charts and slicers that show errors or empty plots | Delete them, or change their data source to a range that still exists |
| Sheet formatting looks right | Check borders, fills, and number formats in the cleared area | Clear formats on those cells separately; this does not affect the source data |
📊 At a glance
People often mix up three separate jobs when they want a pivot table gone. The table compares them so you can tell which one you need before you start.
| Job | What it affects | Order to do it |
|---|---|---|
| Removing the pivot table | Clears the summary layout; leaves the source data alone | Do this step after saving any values you want to keep |
| Keeping the numbers | Copies the displayed results as plain values, since the table itself is not a snapshot | Do this first, before you delete anything |
| Cleaning up formatting | Clears leftover fills, borders, and number formats on the blank cells | Do this last, once the pivot layout is gone |
Doing them in that order avoids a common surprises.
❓ FAQ
1. Will deleting a pivot table delete my source data?
No. The pivot table is a summary layer that reads from a source range, which is usually on a separate sheet. Clearing the pivot cells leaves that range untouched. Confirm it by checking the original sheet after you delete.
2. Why does my formula show #REF! after I removed the pivot table?
The formula was referencing a cell or pivot function in the table you deleted. Find the formula, then point it at the source range or at the pasted values you saved. Searching the workbook for #REF! will show every affected cell.
3. Can I undo a pivot table deletion?
Yes, if you act right away by pressing Undo, or if you saved a copy before deleting. Once you close and reopen the file, undo history is gone, so your saved copy is the reliable fallback.
4. Do I need to remove the formatting too?
Not necessarily. The pivot table and its formatting are separate. If leftover fills, borders, or number formats bother you, clear formats on those cells as a second step.
📝 Summary
To cancel a pivot table in Excel, copy any values you want to keep, select the whole pivot range, and clear it. Your source data stays where it was. Then check for broken formulas and leftover charts so the workbook stays clean.
