How to Reset a PivotTable in Excel

Posted by Dan St. Hilaire  /  December 11, 2009  /  Excel   —   1 Comments ↓

Microsoft Office Excel 2007: Clear a PivotTable report or PivotChart report Excel Logo

To remove all report filters, labels, values, and formatting from a PivotTable report, and to start designing the layout all over again, use the Clear All command. This command effectively resets the PivotTable report, but it does not delete it. The data connection, placement of the PivotTable report, and PivotTable cache remain. If there is a PivotChart associated with the PivotTable report, the Clear All command also removes related PivotChart fields, chart customizations, and formatting.

Caution: If you are sharing a data connection or if you are using the same data between two or more PivotTable reports, then using the Clear All command on one PivotTable could also remove the grouping, calculated fields or items, and custom items in the other shared PivotTable reports. However, you are warned before Microsoft Office Excel attempts to remove items in the other shared PivotTable reports, and you can cancel the operation.

PivotTable report

1. Click the PivotTable report.
2. On the Options tab, in the Actions group, click Clear, and then click Clear All.

PivotChart report

1. Click the PivotChart report.
2. On the Analyze tab, in the Data group, click Clear, and then click Clear All.

Note: The Clear All command is not visible when the worksheet that contains the PivotTable report is protected. The Clear All command does not work if you protect the worksheet and then select the Use PivotTable Reports check box in the Protect Sheet dialog box because the Clear All command requires a refresh operation.

Topics: Excel