How to: Clear or Remove a Pivot Table

• Jun 07, 2019
• 2 minutes to read

The examples below demonstrate how to clear a pivot table by removing all its fields or completely delete a report from a worksheet.

Select the task you wish to perform.

• Clear a Pivot Table
• Delete a Pivot Table

Clear a Pivot Table

To remove all fields and formatting from a PivotTable report, use the PivotTable.Clear method. This method resets the pivot table to the initial state before any fields are added to it, but does not delete the report. The data connection, PivotCache, and the report location are preserved.

Note

If a pivot table you wish to clear is based on the same data as other pivot tables in a document, the PivotTable.Clear method will also remove grouping, calculated fields, and calculated items from other shared PivotTable reports.

View Example

PivotTableActions.vb

Dim worksheet As Worksheet = workbook.Worksheets("Report1")
workbook.Worksheets.ActiveWorksheet = worksheet

' Clear the pivot table.
worksheet.PivotTables("PivotTable1").Clear()

PivotTableActions.cs

Worksheet worksheet = workbook.Worksheets["Report1"];
workbook.Worksheets.ActiveWorksheet = worksheet;

// Clear the pivot table.
worksheet.PivotTables["PivotTable1"].Clear();

The following image shows the result of the code’s execution. A blank pivot table is displayed so you can start designing the report’s layout all over again.

Delete a Pivot Table

To delete a PivotTable report from a worksheet, remove it from the worksheet’s PivotTableCollection by using the PivotTableCollection.Remove or PivotTableCollection.RemoveAt method.

View Example

PivotTableActions.vb

Dim worksheet As Worksheet = workbook.Worksheets("Report1")
workbook.Worksheets.ActiveWorksheet = worksheet

' Access the pivot table by its name in the collection.
Dim pivotTable As PivotTable = worksheet.PivotTables("PivotTable1")
' Remove the pivot table from the collection.
worksheet.PivotTables.Remove(pivotTable)

PivotTableActions.cs

Worksheet worksheet = workbook.Worksheets["Report1"];
workbook.Worksheets.ActiveWorksheet = worksheet;

// Access the pivot table by its name in the collection.
PivotTable pivotTable = worksheet.PivotTables["PivotTable1"];
// Remove the pivot table from the collection.
worksheet.PivotTables.Remove(pivotTable);