GETPIVOTDATA Function

Pulls a specific value out of a PivotTable by field and item.

Category

Lookup & Reference

Available in

All versions, Microsoft 365

Returns

A value from a PivotTable

Taught hands-on in Level 5: Expert Techniques & Workbook Architecture · PivotTables module

Overview

GETPIVOTDATA reads a number from a PivotTable using names instead of cell positions. Because it asks for 'Sales for East' rather than 'cell C7', it keeps working when the pivot is rearranged or refreshed.

Excel writes this formula automatically when you type = and click a value inside a PivotTable.

Syntax

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

Arguments

ArgumentRequired?What it does
data_fieldYesThe value field name, in quotes, e.g. "Sales".
pivot_tableYesAny cell inside the PivotTable.
field1, item1, …OptionalPairs of field names and items that describe the value you want.

Examples

Example 1 — Total sales for one region

A PivotTable starting in A3 shows Sales by Region.

D4fx=GETPIVOTDATA("Sales", $A$3, "Region", "East")
ABCD
3RegionSum of SalesEast
4East900900
5West625
6Grand Total1525
=GETPIVOTDATA("Sales", $A$3, "Region", "East") Explain

Example 2 — Driving it from a cell

Replace the hard-coded item with a cell so a dropdown controls the result.

=GETPIVOTDATA("Sales", $A$3, "Region", D3) Explain
Prefer plain cell references? Turn off PivotTable Analyze → Options → Generate GetPivotData.

Common mistakes

  • #REF! errors. The item isn't visible in the pivot (filtered out, or the field was removed).
  • Dates and numbers as items. Pass real dates/numbers, e.g. DATE(2026,1,1), not text that looks like one.

Notes

Kyle Stott

Author

Kyle Stott

Certified Microsoft Excel Expert

Kyle has worked professionally with Microsoft Excel for over a decade and has consulted on and taught Excel best practices to over 400 companies across 30+ countries.