How to Remove Pivot Table Formula in Excel

Chances are, you built a pivot table in Excel to quickly summarize a messy pile of data, but now you need to send a simple, static report to a colleague or use those summary numbers in a different calculation. But when you try to reference a single cell, you're hit with the long and clunky =GETPIVOTDATA() formula. This article will show you several ways to remove the pivot table functionality and its formulas, leaving you with just the clean, simple data you need.

Graphed

Still Building Reports Manually?

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

Watch the Demo

Why Would You Want to Remove a Pivot Table?

While pivot tables are amazing for interactive analysis, there are many practical reasons you might want to convert them into a static range of values. This process is often called "flattening" or "un-pivoting" the data.

• Sharing Static Reports: The person receiving your spreadsheet might not need or want an interactive pivot table. They just need to see the final numbers. Converting it to values ensures the layout and figures won't accidentally change.
• Creating a Source for Other Formulas: Using the GETPIVOTDATA function to reference cells can be frustrating. Flattening the table allows you to use simple cell references (like =B5) in your VLOOKUPs, SUMIFs, or another report.
• Improving Performance and File Size: Pivot tables store a copy of your source data in a "pivot cache," which can make your Excel file quite large. If you have multiple pivot tables based on a massive dataset, converting them to values can dramatically shrink your file size and make your workbook faster.
• Maintaining Formatting and Consistency: By converting a pivot table to values with its formatting intact, you can create a permanent snapshot-in-time report. This guarantees that what you see is what your recipient sees, regardless of their Excel version or if they try to refresh the data.
• Compatibility: When sharing with users of other spreadsheet programs like Google Sheets or older versions of Excel, pivot table functionality doesn't always translate perfectly. A static table of values, however, is universally compatible.

The Quickest Method: Use Copy and Paste Special

For most situations, the simplest way to remove all pivot table formulas and functionality is with a good old-fashioned copy and paste. The trick is to use "Paste Special" to paste only the values and formatting, leaving the underlying pivot table structure behind.

This is the best method for creating a perfect, permanent visual replica of your report.

Step-by-Step Instructions:

1. Select Your Entire Pivot Table It's important to select the entire table, including headers and totals, to get a complete copy. The easiest way to do this is to click any cell inside your pivot table, which brings up the "PivotTable Analyze" tab in the Excel ribbon.

From there, navigate to: PivotTable Analyze > Actions > Select > Entire PivotTable.

2. Copy the Selection Once the table is highlighted, simply press Ctrl + C (or Cmd + C on a Mac) to copy it to your clipboard.

3. Paste as Values in an Open Area Click on an empty cell where you want your new, static table to live. This can be on the same worksheet or a new one entirely. Right-click the destination cell to open the context menu. You’ll see several "Paste Options" with small icons.

Hover over them to see a preview of how your data will look:

• Values (V): Represented by a clipboard with "123". This pastes only the raw text and numbers, stripping away all formatting like colors, bold fonts, and number styles (e.g., "$1,234.56" becomes "1234.56").
• Values & Number Formatting (A): This is often the best choice. It keeps important number styles like currency symbols, commas, and percentage signs, but removes things like cell background colors and borders.
• Values & Source Formatting (E): This is the one you want to create an exact visual replica. It pastes the values while keeping all your original formatting – cell fills, font styles, borders, and number formatting. Your new static table will look identical to the pivot table it came from.

After pasting, you have a perfect static copy of your report. You can now safely delete the original worksheet that contained the pivot table if you no longer need it.

Graphed

Still Building Reports Manually?

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

Watch the Demo

A Proactive Approach: How to Stop Excel From Generating GETPIVOTDATA

Sometimes you don't want to destroy the pivot table itself. You just want to use simple cell references (like =F10) when building formulas that point to your pivot table's results. You can actually turn off the automatic GETPIVOTDATA function generation.

This is extremely useful when you want to keep the pivot table live and refreshable but need more flexibility in your own calculations.

Step-by-Step Instructions:

1. Click inside your pivot table to activate the PivotTable Analyze menu.

2. In the "PivotTable Analyze" tab, look for the "Options" button on the far left. Click the small dropdown arrow next to it.

3. Uncheck the "Generate GetPivotData" option.

That's it! Now, when you create a new formula and click a cell within your pivot table, Excel will use a standard cell reference. For example, clicking on cell D8 will now result in =D8 in your formula bar, not a lengthy =GETPIVOTDATA(...) function. Existing formulas that were already created with GETPIVOTDATA will not change, but all new ones you create will use standard references.

For Repetitive Tasks: Use a VBA Macro

If converting pivot tables to static data is a regular part of your workflow, doing it manually every time can become tedious. A simple VBA macro can automate this process, converting every pivot table on a worksheet to values in one click.

Important Note: Before running a macro that makes permanent changes, it's always an excellent idea to save a backup copy of your workbook first.

Graphed

Still Building Reports Manually?

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

Watch the Demo

How to Set Up the Macro:

1. Open the VBA Editor: Press Alt + F11 to open the Visual Basic for Applications editor.

2. Insert a New Module: In the VBA editor, go to the menu and click Insert > Module. This will open a blank white code window.

3. Copy and Paste the Code: Copy the code below and paste it directly into the module window.

Sub ConvertAllPivotsToValues()
    ' This macro converts every pivot table on the current sheet to a static table of values.
    
    Dim pt As PivotTable
    Dim ws As Worksheet
    
    ' Set the worksheet to the one you're currently viewing.
    Set ws = ActiveSheet
    
    ' Quick check to ensure the sheet is not protected.
    If ws.ProtectContents Then
        MsgBox "The sheet is protected. Please unprotect the sheet and try again.", vbExclamation
        Exit Sub
    End If
    
    If ws.PivotTables.Count = 0 Then
       MsgBox "No pivot tables were found on this sheet.", vbInformation
       Exit Sub
    End If
    
    ' Loop through each pivot table on the active sheet.
    For Each pt In ws.PivotTables
        ' Selects the entire data area of the pivot table and copies it.
        pt.TableRange2.Copy
        
        ' Pastes over the original location as values while keeping all source formatting.
        pt.TableRange2.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, _
            Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    Next pt
    
    ' Clears the "marching ants" from the clipboard after pasting.
    Application.CutCopyMode = False
    
    ' Let the user know the process is finished.
    MsgBox ws.PivotTables.Count & " pivot table(s) on this sheet have been converted to values.", vbInformation
End Sub

4. Run the Macro:

• You can run the macro directly from the editor by clicking anywhere inside the code and pressing F5.
• Alternatively, you can close the VBA editor (Alt + F11), go to the "Developer" tab on the Excel ribbon, click "Macros," select ConvertAllPivotsToValues from the list, and click "Run."

A message box will appear confirming how many pivot tables have been converted. Once you run it, the change is permanent, and the pivot functionality on that data is gone.

Graphed

Still Building Reports Manually?

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

Watch the Demo

How to Remove a Calculated Field Formula

Sometimes when people say "pivot table formula," they're talking about a custom Calculated Field they've added – like a "Commission" field that multiplies 'Sales' by 5%. If you just want to remove one of these custom formulas, you don't need to flatten the whole table.

To remove a Calculated Field:

1. Click anywhere inside your pivot table.
2. Navigate to the "PivotTable Analyze" ribbon.
3. Find the "Fields, Items, & Sets" button and click it, then select "Calculated Field...".
4. In the dialog box that appears, click the "Name" dropdown to see a list of all custom calculated fields.
5. Select the field you want to get rid of and click the Delete button. Click OK to close.

This will remove the calculated field and its associated formula from your pivot table field list completely.

Final Thoughts

Dealing with GETPIVOTDATA and converting pivot tables to static data is a routine task for anyone who regularly works with Excel reports. For one-off reports, a simple copy and paste using the "Values & Source Formatting" option is fast and effective. If you need dynamic interactivity with simpler formulas, disabling the Generate GetPivotData feature is the best choice. This gives you the best of both worlds - a refreshable pivot table and easy-to-manage formulas.

Ultimately, all these steps in Excel are part of a larger, often manual reporting cycle: exporting raw data from various platforms, cleaning it, building pivot tables, and then flattening those tables for presentation. Here at Graphed, we made it our mission to automate that entire workflow. By connecting directly to your tools like Google Analytics, Shopify, Facebook Ads, and Salesforce, we let you use simple, natural language - "show me my Shopify sales by campaign for the last 30 days" - to instantly create live, shareable dashboards. Instead of spending hours in the copy-and-paste cycle, you can get insights and build reports in seconds with a tool like Graphed.