How to: Clear or Remove a Pivot Table
- 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
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.
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 (the workbook is opened in Microsoft® Excel®). 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.
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);