How to Clear Pivot Table in Excel

A pivot table in Excel is a powerful tool for summarizing data, but sometimes you just need to hit the reset button. Instead of deleting the whole table and starting from scratch, a simple 'clear' can wipe the slate clean, letting you rebuild your analysis from a different angle. This guide will walk you through several effective methods for clearing a pivot table, from a single click to more nuanced approaches.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Why Clear a Pivot Table Instead of Deleting It?

Before diving into the "how," let's quickly cover the "why." Clearing a pivot table is different from deleting it. When you clear it, you remove all the fields, filters, and sorting, returning it to an empty shell. However, the connection to your source data and the pivot table's settings (like its name and data source options) remain intact. This is incredibly efficient for a few common scenarios:

• Starting Fresh: You've analyzed your data one way (e.g., sales by region) and now want to analyze it another way (e.g., sales by product category). Clearing lets you rebuild the structure without creating a new table from scratch.
• Correcting Mistakes: If you've dragged in too many fields or applied complex filters that have become confusing, a quick clear is often faster than undoing each step.
• Creating Templates: You might build a report file with a pre-configured pivot table connected to a data source, ready for a colleague to build their own analysis.

Method 1: The Quickest Way Using "Clear All"

This is the most common and straightforward method to completely reset your pivot table. It takes just a few clicks and will remove every analysis element you've added.

Step-by-Step Instructions:

1. Select the Pivot Table: Click on any single cell inside the pivot table you want to clear. This is an important step, as it brings up the contextual "PivotTable Analyze" tab in the top ribbon. If you don't see this tab, you haven't selected the table correctly.
2. Navigate to the PivotTable Analyze Tab: A new tab titled "PivotTable Analyze" (or simply "Options" in older versions of Excel) will appear in the ribbon. Click on it.
3. Find the "Clear" Option: In the "Actions" group on this tab, you will see a button labeled "Clear". Click the small dropdown arrow on this button.
4. Select "Clear All": From the dropdown menu, choose the Clear All option.

As soon as you click it, Excel will immediately remove all fields from the Rows, Columns, Values, and Filters areas. The result is a blank pivot table canvas, ready for you to drag and drop new fields for your next analysis. All formatting, sorting, and structural elements are gone, but your data connection is still live.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Method 2: Manually Removing Fields and Filters

Sometimes you don't need a complete reset. You might just want to remove a few specific fields or change the layout. In this case, manually clearing elements from the "PivotTable Fields" pane is the better choice. It gives you more control over the process.

How to Manually Remove Fields:

First, ensure the "PivotTable Fields" list is visible. If you don't see it, click inside your pivot table, go to the "PivotTable Analyze" tab, and click the "Field List" button in the "Show" group.

You have two simple ways to remove a field:

• Drag and Drop: In the bottom section of the "PivotTable Fields" pane (where the Rows, Columns, Values, and Filters areas are), simply click and drag the field you want to remove out of its box. Once your cursor is over an empty area of the worksheet, release the mouse button. The field will disappear from both the pane and the pivot table structure.
• Uncheck the Box: In the top section of the "PivotTable Fields" pane, which lists all available fields from your data source, simply uncheck the checkbox next to the field you want to remove. Excel will automatically remove it from whichever area it was assigned to.

This method is perfect when your pivot table is large and you only need to make small adjustments, like swapping out one metric for another or removing a row label to get a higher-level summary.

"Clear All" vs. "Clear Filters": What's the Difference?

In the "Clear" dropdown menu under the "PivotTable Analyze" tab, you'll notice another option: Clear Filters. It's important to understand how this differs from "Clear All" to avoid accidentally wiping out your entire report structure.

• Clear All: This is the total reset button. It removes everything: all fields in the Rows, Columns, and Values areas, plus any filters you've applied. It returns the pivot table to its initial blank state.
• Clear Filters: This option is much more targeted. It only removes the filters that have been applied to the pivot table. The fields defining your rows, columns, and values will remain exactly where they are. This is useful when you want to see the complete, unfiltered dataset without having to rebuild the table's structure.

Think of it this way: use "Clear Filters" when you want to remove the current slice of data to see the whole pie, and use "Clear All" when you want a completely different type of pie.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Using a VBA Macro to Automate Clearing

If you find yourself repeatedly clearing pivot tables as part of a larger workflow, you can automate the process with a simple VBA (Visual Basic for Applications) macro. This is especially useful for standardizing reports or cleaning up a file before sending it to others.

Example VBA Code to Clear a Pivot Table:

To use this code, you'll first need to open the VBA Editor by pressing Alt + F11 in Excel.

Sub ClearSinglePivotTable()

' A script to clear a specific pivot table in your workbook
' First, define the worksheet and pivot table variables
Dim MyWorksheet As Worksheet
Dim MyPivotTable As PivotTable

' === IMPORTANT ===
' Update these names to match your file
Set MyWorksheet = ThisWorkbook.Sheets("YourSheetName Here")
Set MyPivotTable = MyWorksheet.PivotTables("YourPivotTableName Here")

' This command clears all fields and filters
MyPivotTable.ClearAllFilters
MyPivotTable.ClearTable

' Optional: Display a message when done
MsgBox "The pivot table '" & MyPivotTable.Name & "' has been cleared."

End Sub

How to Use the Code:

1. In the VBA Editor, go to Insert > Module to create a new module.
2. Copy and paste the code above into the module window.
3. Crucially, you must update two lines: change "YourSheetName Here" to the exact name of the worksheet containing your pivot table, and change "YourPivotTableName Here" to the name of your pivot table. (You can find your pivot table's name by clicking on it and looking in the "PivotTable Name" box on the far left of the "PivotTable Analyze" tab).
4. You can run the macro from the VBA Editor (by pressing F5) or by assigning it to a button on your worksheet for easy access.

Troubleshooting Common Issues

Sometimes things don't go as planned. Here are fixes for a couple of common issues you might run into when clearing or managing a pivot table.

The "Clear" Button is Grayed Out

This is the most frequent issue and has a simple solution. If the "Clear All" or "Clear Filters" buttons are grayed out, it's because you haven't actually selected a cell within the pivot table. Your cursor is somewhere else on the worksheet.

The Fix: Just click anywhere inside your pivot table. The "PivotTable Analyze" tab will appear, and the buttons in the "Actions" group will become active.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Old Items Still Appear in Filters After Refreshing

Have you ever deleted a row from your source data, refreshed your pivot table, but the old item still appears as a filter option? This is a default Excel behavior designed to preserve historical filter settings, but it can be frustrating.

The Fix: Change PivotTable Options

1. Right-click anywhere inside your pivot table and select PivotTable Options...
2. In the dialog box that opens, click on the Data tab.
3. Look for the setting "Number of items to retain per field." By default, it's set to Automatic. Change this dropdown to None.
4. Click OK.

Now, when you refresh your pivot table, any items that no longer exist in the source data will disappear from your filter lists completely.

Final Thoughts

Clearing a pivot table is a fundamental step in dynamic data analysis in Excel. Using the "Clear All" feature provides a quick way to completely reset your view, while manually removing fields gives you granular control for smaller adjustments. Understanding these techniques saves you time and allows you to explore your data more efficiently.

While mastering pivot tables is a valuable skill, we know the process of manually building, clearing, and re-building reports can still feel repetitive. We built Graphed to do this heavy lifting for you. Rather than clicking and dragging fields, you can simply ask in plain language, "Show me last month's conversion rate by campaign," and get an interactive chart instantly. We make getting insights a 30-second conversation, not a complex series of steps in a spreadsheet.