Are you done with your Excel reports using the Pivot Table? You can delete Pivot Table on your Excel sheet after summarizing large amounts of your data. Once you delete the Pivot Table, you can share the Excel sheet without any hassle. There are three different methods to delete your Pivot Table in your Microsoft Excel – on Windows, on the Web, and using VBA code. In addition, you can also delete the table without removing the data, or you can remove the data to keep the Pivot Table.
Basically, a Pivot table is used to consume your time, and it is easy to use. It creates instant data and segments the data to analyze it easily. Pivot Table has its own formula and calculations to understand the metrics and provides accurate reports.
Contents
How to Delete Pivot Table in Excel on Windows
1. On your Windows PC, open Microsoft Excel or Microsoft Excel 365.
2. Click on the cell to enable the PivotTable Tools on the top of the ribbon.
3. Select Analyze tab and then click on the Select icon to expand it.
4. Next, select the Entire PivotTable option from the drop-down menu list.
5. Then, press the Delete key on your keyboard.
If you see the “We can’t change this part of a PivotTable report” message on the screen, check that you have selected the entire PivotTable. Otherwise, press CTRL+ A to select the entire worksheet and then press the Delete button.
How to Delete Pivot Table in Excel on Web
If you are using Pivot Table on the Excel Web, you can delete it easily by selecting the entire data by pressing the CTRL + A hotkeys on your Keyboard and then pressing the Delete button.
To remove the particular sheet from your workbook, Right click on the Sheet tab at the bottom and then click the Delete option.
How to Remove Pivot Table without Deleting Data
Sometimes, you just need the summarized data from your spreadsheet. In such a scenario, you can delete the Pivot Table without removing the data on your Excel sheet. To do this, follow the steps given below.
1. Click on any cell on the Pivot table.
2. Under the PivotTable Tools ribbon, select the Analyze tab.
3. Click on the Select option and then expand it.
4. Then choose the Entire PivotTable option.
5. Now, right-click on any cell from the selected Pivot Table.
6. Choose the Copy option to copy the data on the Pivot table.
7. Click on the Home tab at the top.
8. Next, select the Paste option and click on the drop-down arrow to expand the option.
9. Navigate to the Paste Values section and select the first icon to paste as values.
Tip! You can clear the Clipboard in Excel to delete the copied data.
Once you have copied the entire data, it will automatically delete the Pivot Table that you have created. A keyboard shortcut to paste the copied data as values, press Alt + E + S + V + Enter hotkeys one after the other.
Note: If you use Ctrl + C and Ctrl + V to copy and paste the data on Excel, it will copy the data and creates another pivot table when you paste it. To avoid this issue, paste the data ad Values.
How to Remove Data on Pivot Table
If you don’t want the data that you have restated from the worksheet or workbook, you can remove the data alone without deleting the Pivot Table.
1. On your Excel worksheet, select any cell in the Pivot Table.
2. On the top of the PivotTable Tools ribbon, select the Analyze tab.
3. Click on the Clear option in the Action group.
4. From the drop-down menu, select the Clear All option to clear the data without deleting the Pivot Table.
How to Delete Pivot Table in Excel Workbook Using VBA Code
If you have created multiple Pivot tables and you would like to delete all those tables at a time, then you can use VBA (Visual Basic for Application) code to do this.
1. Launch Excel and open the workbook that you want to delete Pivot Tables.
2. On your Keyboard, press Alt + F11 to open VBA Editor window.
3. On the left side panel of the window, expand VBAProject and then choose the workbook.
4. Right-click on it and select the Insert option from the submenu list.
5. Then click on the Module option to launch the new module for your current workbook.
6. A Module window will open on the right, and copy and paste the code given below.
Sub DeleteAllPivotTables()
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
7. Click the Green Play button on the top of the VBA Editor toolbar to run the code.
After running the code successfully, all the pivot tables on your Excel workbook will be deleted simultaneously. But keep in mind using VBA Code to remove the Pivot Table cannot be reversed, and it deletes the table permanently.
How to Remove Fields from the PivotTable
There are three methods to remove fields from the PivotTable, and you can follow any one of the methods given below.
- Under the Choose fields to add to the report box, Uncheck the fields that you would like to remove from the PivotTable.
- Click the field in the layout area and then click the Remove Field option to delete the fields.
- Click and hold the field in the layout area and then pull it out from the PivotTable Field List.
Frequently Asked Questions
Press Cmd + A keys on your Mac keyboard to select the entire data and then Copy and Paste the data using the Paste Values option. Now your pivot table will be deleted successfully.
Select the Cell in the worksheet → Analyze tab → Fields, Items, & Sets → Calculated Items →Select the Formula → Delete → Close.
