GETPIVOTDATA function in Excel

Point at a pivot's total cell from inside another formula (type =, then click the pivot cell) and Excel does something surprising: instead of =B5 it writes something like

=GETPIVOTDATA("Revenue", $A$3, "State", "Maharashtra", "Month", "Jan")

How does GETPIVOTDATA work, and why won't it fill down?

That's a structural lookup into the pivot. Instead of fetching "cell B5", it fetches "the Revenue value where State = Maharashtra and Month = Jan," by label. So if the pivot re-sorts, a row moves, or you insert a new state above, the formula still returns the right number, because it matched on meaning instead of position. It's the same "match by name, not by counted offset" robustness you learned to prize in the lookups lessons. For a fixed cell on a summary sheet, it's what you want, say a KPI tile that must always read "total software revenue" however the pivot is rearranged. The signature is GETPIVOTDATA(data_field, pivot_table, [field1, item1], [field2, item2], …).

The trouble starts when it inserts itself and then refuses to drag. Because every field/item pair is literal text, a GETPIVOTDATA formula copied down or across doesn't adjust. Each filled cell keeps returning Maharashtra/Jan. Say you're building an ordinary grid of references off a pivot, pointing cell after cell at successive pivot cells and filling down. GETPIVOTDATA sabotages you by freezing every reference on the first cell you clicked. Beginners fight this for an hour without knowing what's happening.

How do you turn GETPIVOTDATA off, or use it on purpose?

Two habits sort it out:

1. Turn it off when you need to drag. Click inside a pivot, then PivotTable Analyze ▸ Options (click the small dropdown arrow on the Options button) and untick "Generate GetPivotData." Now clicking a pivot cell in a formula yields a plain, relative =B5 that fills like any reference. Toggle it back on when you want the structural behaviour.
2. Use it on purpose, typed deliberately. For durable single-cell KPIs, write GETPIVOTDATA yourself (or point-click with the option on) so the cell self-heals when the pivot layout changes. There's also a middle ground that's both stable and somewhat draggable. Feed the item arguments from cells, e.g. =GETPIVOTDATA("Revenue", $A$3, "State", $H2, "Month", I$1), and a mixed-reference grid of $H2/I$1 labels drives an auditable panel of pivot reads that does fill correctly. It's the auditor-friendly way to build a report off a pivot.

So GETPIVOTDATA is a feature when the cell must be stable and a nuisance when the reference must move. Know which one you're doing, and set the toggle to match. Otherwise you'll be surprised by whichever behaviour Excel hands you.