To clear a PivotTable in Excel for Microsoft 365 on Windows, click inside it and choose PivotTable Analyze > Clear > Clear All to reset its layout, or choose PivotTable Analyze > Clear > Clear Filters to remove filters only. To remove the PivotTable from the worksheet, select the whole table and press Delete; its source data is not deleted.
What “clear a PivotTable” can mean
Excel uses “clear” for several different jobs, and choosing the wrong one can remove more than you intended. A PivotTable is a report built from source data; clearing a filter, resetting the report, and deleting the report are separate actions. First decide what you want the worksheet to look like afterward.
- Show all records again: clear the PivotTable’s filters, including report, label, or value filters that are hiding items.
- Start the report layout over: use Clear All to remove the fields and other PivotTable setup so you can build a different view.
- Remove the report from the sheet: select the entire PivotTable and delete it.
- Remove or change the underlying records: edit the source range or table. Clearing the PivotTable does not do this.
This distinction matters when a report appears empty. It may still be a functioning PivotTable with a restrictive filter, rather than a deleted or damaged table. Conversely, deleting the report is unnecessary if the only problem is a selected month or category.
Before you clear anything
Click a cell in the PivotTable and check the contextual PivotTable Analyze tab. If that tab does not appear, the selected cell may not be part of a PivotTable. If the report has a useful layout, note its row, column, value, and filter fields before using Clear All. You can also duplicate the worksheet first: right-click its sheet tab, choose Move or Copy, select Create a copy, and confirm.
Check whether the report has a connected PivotChart, slicer, or timeline. These controls can affect what you see, and a PivotChart depends on its PivotTable. A chart or control may no longer be useful if you reset or delete the report. If the workbook is shared, consider whether another person relies on the layout or filters before changing it.
Most importantly, identify the source data if you may need to rebuild the report. It could be a table or range in the same workbook, a connection, or a Data Model. The PivotTable itself is a summary, not the original records. Clearing it is not a substitute for editing or backing up those records.
Clear filters while keeping the report
To remove filters from a PivotTable without clearing its field layout, click inside the report and choose PivotTable Analyze > Clear > Clear Filters. This is usually the right choice when rows or totals have disappeared because a report filter, label filter, or value filter is active. The fields and arrangement remain in place; the report can show the items allowed by its source and other connected controls again.
You can also clear an individual filter from its field drop-down. Open the arrow beside the row, column, or report-filter field and choose Clear Filter From followed by that field’s name, if the option is available. This is useful when you want to remove one condition but keep another. For example, a sales PivotTable filtered to the West region and to one month can be returned to all regions while leaving the month selection intact.
A slicer or timeline has its own selection state. If its buttons still show a restricted selection after you use Clear Filters, click the slicer’s Clear Filter icon, commonly shown as a funnel with a red X. A PivotTable may also be connected to multiple slicers or reports, so inspect the controls that are actually connected to it. If the total still looks incomplete, check for filters on other fields rather than assuming Excel failed to clear the first one.
Reset the layout with Clear All
Use Clear All when the goal is to empty the PivotTable setup, not merely reveal hidden items. Select a cell in the PivotTable and choose PivotTable Analyze > Clear > Clear All. Excel clears the report’s field arrangement and its PivotTable setup, leaving you able to add fields again. The source records are not erased by this command. Because the report is reset, any filters, formatting, or layout choices you wanted to keep may need to be restored.
For example, imagine a PivotTable with Region in Rows, Quarter in Columns, and Sum of Sales in Values. Clear Filters would keep that summary structure and remove filtering that limits the displayed regions or quarters. Clear All resets the report instead; to recreate it, add Region, Quarter, and Sales back to the appropriate areas in the PivotTable Fields pane. That difference makes Clear All a poor first step when the report merely looks incomplete.
After clearing, the field list can be used to rebuild the report. Drag a text field such as Region to Rows, a date or category field to Columns, and a numeric field to Values. Excel generally summarizes numeric values as a sum, but it may use a count when the data is not consistently numeric. Check the value field’s summary before relying on totals. If the field list is missing, click inside the PivotTable and look for PivotTable Analyze > Field List.
Delete the PivotTable from the worksheet
If you want the PivotTable gone rather than reset, select the entire report. Click inside it, then choose PivotTable Analyze > Select > Entire PivotTable. Press Delete on the keyboard. Selecting the full report first helps avoid clearing only a cell or a small part of the worksheet selection. Confirm that the highlighted area covers the PivotTable before you delete it.
Deleting the PivotTable removes the report from the worksheet, but it does not delete the source table or range. It also does not necessarily remove all workbook storage associated with PivotTable caches or connections. Therefore, deleting the visible report may not noticeably reduce file size, especially if the workbook contains other PivotTables that use the same source. Cache and connection management is a separate issue from removing a report.
If Delete does not behave as expected, verify that the entire PivotTable is selected and that the sheet is not protected. A protected worksheet can prevent edits. If only a portion of the report is selected, Excel may refuse the action or leave part of the layout behind. If you need to preserve the report temporarily, copy the worksheet or save a separate workbook before deleting it.
Keep the source data and rebuild safely
PivotTables summarize data; they do not normally serve as a replacement for the original records. If the source is an Excel table, the table remains when you clear or delete a PivotTable. If it is a worksheet range, that range remains too. For an external connection or Data Model source, the data may be stored or retrieved differently, so inspect the workbook’s data connections rather than expecting the report itself to contain editable source rows.
To rebuild after a reset, click the blank PivotTable area, open the field list, and place fields into Rows, Columns, Values, and Filters as needed. If you deleted the report entirely, create a new one through Insert > PivotTable, then choose the source and destination in the dialog. For a source organized as a simple rectangular range, include its header row and avoid blank column headings. Converting a changing source range to an Excel table before creating a new PivotTable can make later additions easier to include.
When source records change, use PivotTable Analyze > Refresh to update a report, or Data > Refresh All to refresh workbook connections and reports together. Refresh is different from clearing: it recalculates the summary from its source, but it does not necessarily remove a filter or redesign the field layout. If new rows are missing after a refresh, check whether the PivotTable’s source includes those rows.
Common problems and what to check
- The PivotTable still looks filtered: inspect each field’s drop-down, report filters, slicers, and timelines. More than one control can limit the displayed items.
- The table looks blank after Clear All: that is consistent with resetting its fields. Open the field list and add the fields needed for a new report.
- Totals do not match the source: check whether the report is filtered, whether the value field uses Sum or Count, and whether the source range includes all records.
- New source rows do not appear: refresh the report and verify the source range or table. Refresh cannot include records that lie outside the PivotTable’s defined source.
- You cannot delete or change the report: confirm that the whole PivotTable is selected and that the worksheet is not protected. Merged cells or other worksheet constraints can also complicate layout changes.
One frequent misconception is that deleting a PivotTable deletes the data it summarizes. It does not remove ordinary source rows. The reverse misconception is that Clear All removes only visible numbers: it resets the PivotTable’s setup as well, so it is more disruptive than clearing a filter. Choosing the action based on the intended result avoids rebuilding a report unnecessarily.
Windows, Mac, and Excel for the web
The ribbon paths above describe Excel for Microsoft 365 on Windows. In Excel for Mac, select a cell in the PivotTable to show its contextual PivotTable tab; the tab’s name and ribbon arrangement can vary slightly by release. Look for the clear options on that tab, and use the Mac keyboard’s Delete key where a keyboard deletion is needed. Because keyboard behavior can differ by keyboard layout, the ribbon command is a useful alternative.
Excel for the web has PivotTable tools too, but the available commands and selection behavior can differ from the desktop app. If you cannot find Entire PivotTable or a particular clear command in the browser, open the workbook in the desktop version of Excel and use the steps above. In any version, make a copy first if the PivotTable or its connected chart is important.
Frequently Asked Questions
How do I clear a PivotTable without deleting it?
To remove filters but retain the report, choose PivotTable Analyze > Clear > Clear Filters. To reset the field layout, choose Clear All; that resets more than the filters.
Does deleting a PivotTable delete the source data?
No. Deleting the report removes it from the worksheet, not the source table or range it summarizes. External connections and Data Model sources are separate workbook components.
How do I remove all filters from an Excel PivotTable?
Click the PivotTable and use PivotTable Analyze > Clear > Clear Filters. Also clear selections on connected slicers or timelines if they continue to limit the results.
Why is my PivotTable blank after I cleared it?
If you chose Clear All, the field layout has been reset, so there may be no fields left to display. Open the field list and add the desired fields to Rows, Columns, and Values.
Can I undo a PivotTable deletion?
Use Undo immediately if it is available, typically with Ctrl+Z on Windows or Command+Z on Mac. If the workbook has been closed or the undo history is no longer available, restore a saved copy or recreate the report from its source.
Related
- Excel NOW as Text: Show the Current Date and Time
- Excel Current Year: How to Get It with NOW and TODAY
- Excel NOW Format: Show the Current Date and Time Correctly
Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.
More in Charts & pivot tables
- Excel Gantt Chart Template With DependenciesYes. You can make an Excel Gantt chart template with dependencies by keeping tasks, durations, predecessor IDs, and…
- Excel Chart Won’t Update With New Data? Causes and FixesIf an Excel chart will not update with new data, first check whether its source range includes the…
- Excel Chart Multiple Series: Add and Format ThemYes. To chart multiple series in Excel for Microsoft 365 on Windows, arrange each series in its own…
- Excel Histogram with Multiple Series: Methods That WorkExcel’s built-in Histogram chart is designed for one data series at a time, so selecting several columns will…
- Excel Graph: Hide Zero Values Without Hiding DataTo hide zero values in an Excel graph while keeping the underlying data, create a helper column that…
- Reverse Waterfall Chart in Excel: How to Make OneTo make a reverse waterfall chart in Excel for Microsoft 365 on Windows, enter the starting total and…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.