In this article, I will show you how to delete the pivot table in Excel. The pivot table is very important when we want to analyze or slice data to get insight. Employees who analyze data regularly, cannot think of a day without a pivot table. This is a special part of Excel. The pivot table is the single best way to analyze data.
Download Practice Workbook
You can download the practice workbook from here.
3 Easy Methods to Delete a Pivot Table in Excel
There are 3 methods through which we can delete a pivot table in Excel. We can the entire pivot table or we can keep the data but delete the table. Since we use pivot tables to analyze a dataset, after our analysis is done, we may need to delete all the pivot tables as well. So each of these methods satisfies our criteria. All of these methods are below with proper steps. For the entire demonstration, we will use the following dataset.
From the data, we got the following pivot table.
1. Delete Pivot Table with Table Data
We can delete the entire pivot table along with the data at once. To do so, we will follow these steps.
Steps:
- At first, we will select the full table. In our case, the cell range is from B4 to E9.
- Then we will press Delete on the keyboard to delete the entire pivot table.
- Or we can go to the PivotTable Analysis tab in the Ribbon and select the Entire Pivot Table under the Select section. Then we will press Delete on the keyboard to delete the entire table with data.
2. Delete Pivot Table without Table Data
There are two main steps in this process. The first one is to keep the data in another place like other cells or sheets. The second one is to remove the pivot table itself. There are several steps in these two steps. We will show the steps with a visualization of the process below.
Steps:
- First, we will go to the PivotTable Analyze tab (it is only available when you have selected a cell of the pivot table report) and click on the Select menu and choose the Entire Pivot_Table option.
- Second, press Ctrl+C for copying the entire pivot table data. Select a cell in the same worksheet or any worksheet where you want to keep this data.
- Third, press Ctrl+V for pasting the data.
- After that, click on the Ctrl You will find several options. Just choose the Value (v) option from the Paste Values section.
- Finally, we get the following pivot table raw data. Now we will delete the pivot table.
- To do so, we will select the entire table.
- Consequently, press Delete on the keyboard to delete the pivot table.
We will see the entire pivot table has been deleted from the worksheet. Here we deleted the entire pivot table without loosing data.
3. Applying VBA Code to Delete All the Pivot Tables
When we need to delete all the pivot tables in a workbook at once, we will follow this method.
Steps:
- Firstly, we will press Alt+F11. A window named Microsoft Visual Basic for Application will appear.
- Secondly, in the window, click on Insert and select Module.
- Thirdly, in the writing space, copy and paste the following code.
Sub del_piv_table()
Dim w_s As Worksheet
Dim Piv_table As PivotTable
On Error Resume Next
For Each w_s In ActiveWorkbook.Worksheets
For Each Piv_table In w_s.PivotTables
w_s.Range(Piv_table.TableRange2.Address).Delete Shift:=xlUp
Next Piv_table
Next w_s
End Sub
- Next, run the code using the Run Button in the options bar. We will see all the pivot tables in the entire workbook have been deleted.
How to Move Pivot Table in Excel
If we want to move the pivot table, we will follow these steps:
- At first, we will select the pivot table and go to the PivotTable Analyze tab in the Ribbon.
- Then select Move PivotTable in the Actions A small dialog box will appear asking where to move the table. Here we will select the Existing worksheet to keep the table in the same worksheet.
- At last, we will select the cell where we want to move the table. In our case we choose F4. Pressing OK will immediately move the table to the desired destination.
- Finally, we will get the pivot table moved to the desired F4 cell like the below image.
How to Delete a Pivot Table Field in Excel
To delete a pivot table field, we will follow these steps:
- Firstly, we will select the pivot table and go to the PivotTable Analyze tab in Ribbon.
- Secondly, clicking on the Field List in the Show a side panel will appear.
- Thirdly, in the side panel, we will untick the field we do not need. The field will be deleted from our table. In our case, we want to delete the Sum of Amount field.
- Finally, the pivot table will have the field deleted like the below image.
Things to Remember
- The VBA method will permanently delete all the pivot tables in the workbook, and they are irrecoverable. So, it’s better to make a backup beforehand.
- The demonstration was made in Excel 365. So, the interface may vary for different versions.
Conclusion
There is no direct way to delete the pivot table without losing data. This is the easiest way to delete the pivot table and keep all the data of the deleted pivot table. If you’re still having trouble with any of these methods, let us know in the comments. For any excel related problems, you can visit our website Exceldemy for all types of excel related problem solutions.
thank sir
Always very helpful and useful lessons . Thanks you!!!
Thanks for your feedback 🙂
sometimes simple things are the biggest problems…super sir, thanks
Thanks, Pedja for your feedback.
Best regards.