How to Remove Pivot Table Format in Excel

A PivotTable is one of Excel's most powerful tools for summarizing a large dataset, but sometimes you just need a simple, flat table of its results. Getting your nicely summarized data out of the PivotTable format can be tricky, as it often brings along awkward layouts, gaps, and subtotals. This tutorial will walk you through a few clean and easy methods to remove PivotTable formatting and convert your summary into a standard Excel range.

Graphed

Still Building Reports Manually?

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

Watch the Demo

Why Remove PivotTable Formatting?

While PivotTables are fantastic for interactive analysis, you'll often need to 'flatten' them into a regular table for other purposes. Here are a few common reasons:

• Further Calculations: You want to perform calculations or use formulas that aren't easily done within the PivotTable structure.
• Charting Data: Standard charts work best with a simple, static range of data, not a dynamic PivotTable.
• Sharing Reports: You need to send a simple summary to a colleague or client who doesn't need or want the complexity of a live PivotTable. Converting it to values makes the file cleaner and easier to read.
• Creating a Snapshot: You want to save a summary from a specific point in time without it changing when the source data is updated.
• Data Export: You need to export the summarized data as a CSV or to another application that can't read a PivotTable.

The Quickest Method: Copy and Paste Values

The most straightforward way to remove the PivotTable format is to copy its contents and paste them elsewhere as static values. This breaks the link to the original data source, leaving you with just the numbers and text.

Step 1: Select Your Entire PivotTable

Click anywhere inside your PivotTable. Then, press Ctrl + A on your keyboard to select all of its contents, including the headers and totals.

Step 2: Copy the Selected Data

With the entire PivotTable selected, press Ctrl + C to copy it to your clipboard.

Graphed

Still Building Reports Manually?

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

Watch the Demo

Step 3: Choose a Destination and Use Paste Special

Navigate to a new worksheet or a blank area of your existing sheet where you want the flattened data to go. Right-click on the cell where you want to paste the data (this will be the top-left cell of your new table).

In the context menu, hover over "Paste Special" and select the "Values" icon (it looks like a clipboard with "123"). This will paste only the text and numbers, stripping away all formatting and the underlying PivotTable functionality.

Pro Tip: You can also choose "Values & Number Formatting" if you want to keep formatting like currency symbols, percentages, and decimal places but still remove the PivotTable structure.

Before and After

You have now successfully converted the PivotTable into a regular range of data. However, the result often looks messy, with blank cells in the outer row labels. The next section will show you how to quickly clean this up.

How to Clean Up Your Pasted Data

The copy-and-paste method is fast, but it leaves you with a table that has gaps and possibly unwanted totals. A perfectly flat table has a value in every single cell, making it easy to filter, sort, and analyze. Here’s how to fix those gaps.

The "Go To Special" Trick to Fill in Blanks

PivotTables display repeated row labels only once, leaving blank cells underneath them. Manually filling these in would be a nightmare for a large table, but Excel's "Go To Special" feature can do it in seconds.

1. Select the Columns with Gaps: Highlight the columns in your newly pasted data that contain the blank cells. For example, if you have empty cells in your 'Region' and 'Product' columns, select both of them.
2. Open "Go To Special": Press Ctrl + G (or F5) to open the "Go To" dialog box. Click the "Special..." button in the bottom left corner.
3. Select Blanks: In the "Go To Special" window, choose the "Blanks" option and click OK. Excel will now highlight only the empty cells within your original selection.
4. Enter a Simple Formula: With the blank cells still selected, don't click anything else. Type the equals sign (=) and then press the Up Arrow key on your keyboard. Your formula bar should now show a reference to the cell directly above the first blank cell (e.g., =A3).
5. Apply to All Blanks: Now for the magic. Instead of pressing Enter, press Ctrl + Enter. Excel will instantly copy that formula down into every selected blank cell, automatically filling in all the gaps by carrying down the label from the cell above.

Your table is now formatted correctly, with no gaps in the row labels!

Graphed

Still Building Reports Manually?

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

Watch the Demo

A Proactive Method: Prepare Your PivotTable Before Copying

Instead of cleaning up the data after you paste it, you can adjust your PivotTable's design first. This will give you a clean, flat table right from the start, often eliminating the need for any cleanup.

Click anywhere inside your PivotTable to reveal the "PivotTable Analyze" and "Design" tabs in the ribbon.

Step 1: Change the Report Layout to Tabular

The default "Compact" layout tries to save space by cramming multiple fields into one column. The "Tabular" layout gives each field its own dedicated column, which is exactly what we want for a flat file.

• Go to the Design tab.
• Click on Report Layout.
• Select Show in Tabular Form.

Step 2: Repeat All Item Labels

This simple setting instantly solves the "blank cell" problem we fixed earlier with "Go To Special." It forces the PivotTable to carry down and repeat the labels for each row.

• Go to the Design tab.
• Click on Report Layout again.
• Select Repeat All Item Labels.

Step 3: Turn Off Subtotals and Grand Totals

If you don’t need the summary rows in your final table, it’s best to turn them off now.

• On the Design tab, click Subtotals and select Do Not Show Subtotals.
• Next, click Grand Totals and select Off for Rows and Columns.

Your PivotTable now looks like a standard, clean table. You can now follow the same copy-and-paste-values method from before (Ctrl+A, Ctrl+C), and your pasted data will be perfectly formatted with no gaps or extra rows to delete.

Graphed

Still Building Reports Manually?

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

Watch the Demo

What If My End Goal Is a CSV File?

If you plan to export the data as a CSV anyway, the process is even simpler. Comma-Separated Values (.csv) files can't store PivotTable functionality, so the format is removed automatically during the save process.

1. Prepare your PivotTable for copying, preferably using the proactive layout adjustments from the previous section to ensure it's in a clean, tabular format.
2. Select the entire PivotTable (Ctrl+A) and copy it (Ctrl+C).
3. Open a new, blank Excel workbook.
4. Paste the data as values into the new workbook using "Paste Special."
5. Go to File > Save As.
6. From the "Save as type" dropdown menu, select CSV (Comma delimited) (*.csv).
7. Give your file a name and click Save. Excel will warn you that some features might be lost, click "Yes" to proceed.

This process results in a simple text file containing your raw data, with all formatting, formulas, and connections stripped away.

Final Thoughts

Converting a PivotTable back into a regular range of data might seem complicated, but it's as simple as a copy and paste once you know how to handle the cleanup. By using the built-in "Go To Special" tool to fill blanks or correctly adjusting the table design beforehand, you can easily create the static, flat tables you need for reports, charts, and further analysis.

We know that spending hours pulling data out of different platforms like Google Analytics or your CRM, cleaning it in spreadsheets, and manually building these reports every week is exhausting. We experienced this exact thing, which is why we built Graphed. You can connect all your marketing and sales data sources with a few clicks and then simply ask for the dashboards and reports you need in plain English. Your dashboards stay updated in real-time, giving you back your time to focus on strategy, not spreadsheet wrangling.