How to Turn Off Pivot Table in Excel

PivotTables are a powerful tool for summarizing and analyzing data in Excel, but sometimes you need to lock that data in place. You might want a static snapshot for a presentation or need to share a report without the interactive functionality. This article will show you exactly how to "turn off" or convert your PivotTable into a regular, static range of data, preserving all your values and formatting.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

Why Turn a PivotTable into a Static Table?

While the interactivity of a PivotTable is its main strength, there are several good reasons to convert it back to a normal data range. This process is essentially "unlinking" the summary table from its source data and its dynamic structure.

You might want to do this to:

• Create a point-in-time report: Freeze the data exactly as it appears at a specific moment for a weekly, monthly, or quarterly report.
• Share data with others: Send a report to colleagues or clients who may not have Excel or who don't need to slice and dice the data themselves. A static table is universal.
• Perform custom calculations: Sometimes you need to add unique formulas or calculations next to your summarized data in a way that is difficult or impossible within the PivotTable structure.
• Apply custom formatting: While PivotTables have styling options, you might need complete control over every cell's color, borders, or font, which is only possible in a standard range.
• Use the summary data as a source: Use the output of your PivotTable as a clean, static source for another analysis, chart, or Excel feature like VLOOKUP.
• Reduce file size: Removing the PivotTable cache and connection to the source data can sometimes help reduce the overall file size of your workbook.

Whatever your reason, the goal is the same: to turn your dynamic PivotTable into a simple, non-interactive block of cells. Let's look at the best ways to do it.

The Easiest Method: Copy and Paste as Values

The fastest and most common way to convert a PivotTable is by using Excel’s reliable Paste Special feature. This method extracts all the values from your PivotTable - including totals and subtotals - and pastes them as a clean, static data set, leaving the original PivotTable untouched.

Follow these simple steps:

Step 1: Select Your Entire PivotTable

First, you need to highlight all the data in your PivotTable. The quickest way is often overlooked.

• Click any single cell inside your PivotTable.
• Press Ctrl + A on your keyboard. This command will intelligently select the entire PivotTable report area, including headers, rows, and grand totals.

Alternatively, you can use the ribbon menu:

• Click a cell within the PivotTable to bring up the PivotTable Analyze tab in the ribbon.
• On that tab, go to the Actions group, click the Select dropdown, and choose Entire PivotTable.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Step 2: Copy the Selected Data

With your entire PivotTable selected, simply copy the data. You can do this by:

• Pressing Ctrl + C, or
• Right-clicking on the selection and choosing Copy.

Step 3: Paste the Data as Values

This is the most important step. Instead of a normal paste (Ctrl + V), you need to paste only the values to break the link to the PivotTable. It's best practice to paste this into a new worksheet or a different area of your current sheet to avoid overwriting anything.

• Click on the cell where you want your new static table to begin, then:

That's it! You now have an exact replica of your PivotTable's data, but it's just a normal range of text and numbers. You can now edit individual cells, delete rows, or apply any custom formatting you want without getting the "Cannot change this part of a PivotTable report" error.

How to Keep Your Formatting (The Best of Both Worlds)

Pasting as values is great, but it strips away all the nice formatting you might have applied - like number formats (currency, percentages), colors, and bolding. If you spent time making your PivotTable look good and want to keep that style, there’s an option for that.

Follow the same Steps 1 and 2 as above (select and copy the PivotTable). Then, in Step 3, you'll choose a different Paste Special option:

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

Step 3 (with Formatting): Paste Values & Source Formatting

1. Right-click on the destination cell where you want your table to go.
2. In the Paste Options section of the context menu, find the icon that looks like a clipboard with "123" and a paintbrush.
3. When you hover over it, the tooltip should say Values & Source Formatting. Click this.

This command pastes the numbers and text while also duplicating the exact formatting from the original PivotTable, including number formats, fonts, cell colors, and borders. The resulting table is still completely static and unlinked from the source data, but it looks identical to the original PivotTable, saving you the time of reformatting everything manually.

Completely Deleting a PivotTable

Sometimes you don't want to convert a PivotTable, you just want to get rid of it entirely to clean up your workbook. Doing this is also straightforward, but be aware that this action is permanent and deletes the summarized report.

How to Delete the Entire PivotTable Report

1. Click on any cell inside the PivotTable you want to delete. This activates the PivotTable Analyze tab in the ribbon.
2. Go to the PivotTable Analyze tab.
3. In the Actions group, click the Select dropdown menu and choose Entire PivotTable.
4. With the whole table highlighted, simply press the Delete key on your keyboard.

The entire PivotTable report will disappear from your worksheet. Importantly, this does not affect your original source data. The raw data that the PivotTable was using for its calculations remains safe and untouched in its original location.

Clearing vs. Deleting a PivotTable

You might also see a "Clear" option in the PivotTable Analyze tab. It's important to know how this differs from deleting.

• Deleting (as described above) removes the entire structure from the worksheet.
• Clearing (found under PivotTable Analyze > Actions > Clear > Clear All) removes all the fields, filters, and values from your PivotTable, but it leaves an empty placeholder on your sheet. This is useful if you want to rebuild the report from scratch in the same spot without having to insert a new PivotTable.

Common Mistakes and Quick Tips

When converting or removing PivotTables, a few common issues can trip people up. Here are some tips to keep in mind.

Mistake: Editing Cells in a Live PivotTable

One of the top Excel frustrations is the error message, "We can't make this change for the selected cells..." This happens when you try to change or delete a single cell that is part of a PivotTable's structured layout. The solution is always to copy and paste the table as values first, which converts it to a standard range that you can fully edit.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Tip: Check Your Totals Before You Copy

When you convert a PivotTable to values, you get a static snapshot of exactly what you see. If your Grand Totals or Subtotals are turned off in the PivotTable view, they won’t be included in your pasted data. Before you copy, make sure your table is configured correctly.

You can manage totals from the Design tab (which appears when you click on the PivotTable):

• Go to Design > Grand Totals to turn them on or off.
• Go to Design > Subtotals to show or hide itemized subtotals.

Configure these how you want the final output to look, then copy and paste.

Tip: Paste your Source Data in a New Sheet

It's worth repeating: converting or deleting a PivotTable has zero impact on your original source data table. This raw data remains exactly as it was, so you don't need to worry about losing it during these operations. The original data and the summarized PivotTable are separate entities.

Final Thoughts

Converting a PivotTable into a static table is a fundamental Excel skill that gives you more flexibility. Whether you're sharing a static report, applying custom formats, or using the data for further work, the straightforward copy-and-paste-as-values technique is the go-to solution.

Manually reshaping reports from PivotTables is often just one small step in a larger data-wrangling journey. The time spent exporting data from different systems, refreshing PivotTables, and ensuring everything is the right version can be exhausting and time-consuming, but utilizing efficient tools can help streamline this process.