Why Does GETPIVOTDATA Keep Appearing in My Formulas? (And How to Turn It Off)

A student building a simple summary formula typed an equals sign, clicked a cell inside a pivot table expecting a normal reference like =B5, and instead watched Excel insert something like =GETPIVOTDATA(“Amount”,$A$3,“Region”,“West”). Dragging that formula down to fill the rest of the column produced the exact same value in every row instead of progressing through the data, which is what finally prompted the question I get asked more than almost any other pivot table question: why does this happen, and how do I make it stop?

What’s Actually Happening When You Click Into a Pivot Table

By default, Excel has a setting called “Generate GetPivotData” enabled. Whenever you start a formula and click a cell that falls inside a pivot table, Excel automatically wraps that reference in the GETPIVOTDATA function instead of inserting a plain cell reference like B5. This is intentional default behavior, not a glitch.

Why This Is Actually a Feature, Not a Bug

GETPIVOTDATA looks up a value by field and item name — “give me the Amount where Region equals West” — rather than by fixed cell position. This means if the pivot table is later refreshed, resorted, or a new region gets added or removed (shifting which row “West” happens to sit in), the formula still finds the correct value, since it is searching by name rather than by a hardcoded row and column position that may no longer point to the right cell.

A plain cell reference like =B5 has no such protection. If the pivot table resorts and West moves from row 5 to row 7, B5 silently returns whatever now happens to occupy that position, which could easily be the wrong region’s total without any visible warning that something has gone wrong.

Why It Becomes a Problem: Dragging a Formula Across Multiple Cells

This same name-based lookup behavior is exactly what breaks the common workflow of dragging a formula across a range to fill several cells at once. A normal cell reference shifts relatively as you drag — B5 becomes B6, then B7. GETPIVOTDATA does not shift the same way, since it is referencing a specific named item (“West”) rather than a relative position, so dragging the formula simply repeats a reference to that same fixed item rather than progressing to the next region in your list.

How to Turn It Off

Click anywhere inside the pivot table, then go to the PivotTable Analyze tab on the ribbon. Click the small dropdown arrow next to the Options button (not the button itself, the arrow beside it). In the menu that appears, find Generate GetPivotData and click it to toggle it off. Once disabled, clicking pivot table cells while building formulas inserts plain cell references instead.

The Quick Workaround Without Changing the Global Setting

If you only need a plain reference occasionally and want to keep GetPivotData enabled the rest of the time, type the cell reference manually instead of clicking the cell — typing =B5 directly, rather than clicking the cell with your mouse, never triggers GetPivotData regardless of the current setting, since the automatic substitution only happens on a mouse click during formula entry.

When You Should Actually Keep GetPivotData Enabled

For formulas pulling one or two specific summary values into a separate report or dashboard cell elsewhere in your workbook, GetPivotData is genuinely the more reliable choice, precisely because it keeps working correctly even after the pivot table is refreshed or rearranged. The cases where it causes real frustration are specifically when you are trying to drag-fill a formula across many cells that reference a pivot table directly — for that specific workflow, disabling it or typing references manually is the better approach.

A Quick Reference

Are you trying to pull pivot table values into a formula and running into this exact issue? Describe what you are trying to build and I can help you figure out whether GetPivotData or a plain reference fits your specific case better.