How to Delete a Pivot Table in Excel (3 Easy Methods)

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.

how to delete a pivot table

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.

Delete Pivot Table with Table Data

  • 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.

Delete Pivot Table with Table 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.

Delete Pivot Table without Table Data

  • 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.

Delete Pivot Table without Table Data

  • To do so, we will select the entire table.

Delete Pivot Table without Table Data

  • 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.

Applying VBA Code to Delete All the Pivot Tables

  • 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

Applying VBA Code to Delete All the Pivot Tables

  • 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.

How to Move Pivot Table

  • 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.

How to Move Pivot Table

  • 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.

How to Delete a Pivot Table Field

  • 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.

How to Delete a Pivot Table 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.

Kawser

Kawser

Hello! Welcome to my Excel blog! It took me some time to be a fan of Excel. But now I am a die-hard fan of MS Excel. I learn new ways of doing things with Excel and share them here. Not only how-to guide on Excel, but you will get also topics on Finance, Statistics, Data Analysis, and BI. Stay tuned! You can checkout my courses at Udemy: https://www.udemy.com/user/exceldemy/

5 Comments
  1. thank sir

  2. Always very helpful and useful lessons . Thanks you!!!

  3. sometimes simple things are the biggest problems…super sir, thanks

Leave a reply

ExcelDemy
Logo