SheetSkill

GETPIVOTDATA function

Reads one number out of a PivotTable by naming it (“Sales for North”), so the formula keeps working when the PivotTable changes shape.

Works in desktop Excel only

Syntax

=GETPIVOTDATA(data_field, pivot_table, [field1, item1], …)

How to use GETPIVOTDATA in Excel, step by step

GETPIVOTDATA doesn’t run in a web practice sheet because it reads a PivotTable, and practice sheets don’t have PivotTables. Follow these steps in Excel itself.

You’ll need

  • A PivotTable in the workbook
  1. Create a PivotTable. Click your data, Insert › PivotTable › OK. Drag Region to Rows and Sales to Values.
  2. Let Excel write it. In an empty cell outside the PivotTable type =, then click a value inside the PivotTable and press Enter. Excel writes the GETPIVOTDATA formula for you.
  3. Read the formula. It looks like =GETPIVOTDATA("Sum of Sales",$A$3,"Region","North"): the value field, any cell in the PivotTable, then field/item pairs.
  4. Make it flexible. Replace "North" with a cell reference (for example E1) so the result changes when you type another region.
  5. Turn it off if you prefer plain references. PivotTable Analyze › Options › untick Generate GetPivotData.

Example

=GETPIVOTDATA("Sum of Sales",$A$3,"Region","North")

Result: 48,250

#REF! means the item isn’t visible in the PivotTable (for example it’s filtered out).