GETPIVOTDATA
Pull a single summarized value out of a pivot table with Excel's GETPIVOTDATA function.
Syntax
=GETPIVOTDATA(DataField, PivotTable, Field1, Item1, [Field2], [Item2], ..., [FieldN, [ItemN]])
Returns: Number Arguments
| Argument | Required | Description |
|---|---|---|
| DataField | Yes | The specific data field or value to retrieve from the pivot table. |
| PivotTable | Yes | A reference to any cell in the pivot table where the data is stored. |
| Field1 | Yes | The name of the first field in the pivot table by which the data is summarized. |
| Item1 | Yes | The specific value or item within the first field to filter the data. |
| Field2 | No | Additional field(s) for further filtering or summarizing the data. |
| Item2 | No | The specific value or item within the respective additional field(s) for data refinement. |
| FieldN | No | Final field for detailed data selection. |
| ItemN | No | The specific value within the last field for precise data extraction. |
About
GETPIVOTDATA returns one value from a pivot table based on the field and item criteria you give it. Instead of pointing at a fixed cell like B5, you tell it which data field to read (for example "Sum of Sales") and which rows or columns to match, so the formula keeps working even after the pivot table is sorted, filtered, or refreshed with new data.
Reach for it when you build dashboards or summary reports on top of a pivot table and need specific numbers in fixed spots, like a KPI tile or a printed header. Excel often writes these formulas for you: type "=" and click a value inside the pivot table, and it fills in the GETPIVOTDATA syntax automatically. You can switch that behavior on or off under PivotTable Analyze > Options > Generate GetPivotData.
If your data lives in a normal range or table rather than a pivot table, conditional functions like SUMIFS or lookups like VLOOKUP and INDEX with MATCH are usually a better fit.
Reach for it when you build dashboards or summary reports on top of a pivot table and need specific numbers in fixed spots, like a KPI tile or a printed header. Excel often writes these formulas for you: type "=" and click a value inside the pivot table, and it fills in the GETPIVOTDATA syntax automatically. You can switch that behavior on or off under PivotTable Analyze > Options > Generate GetPivotData.
If your data lives in a normal range or table rather than a pivot table, conditional functions like SUMIFS or lookups like VLOOKUP and INDEX with MATCH are usually a better fit.
Examples
Regional sales total
=GETPIVOTDATA("Sum of Sales", A3, "Region", "East") Pulls the total sales for the East region from a pivot table whose top-left corner sits at A3. Reorder the regions and the formula still returns the East total.
Sales for one product in one region
=GETPIVOTDATA("Sum of Sales", $A$3, "Region", "East", "Category", "Electronics") Adds a second field and item pair to narrow the result to Electronics sales in the East region. Lock the pivot table reference ($A$3) so you can copy the formula without it shifting.
Dynamic dashboard KPI
=GETPIVOTDATA("Sum of Revenue", $A$3, "Month", DATE(2026,1,1), "Department", $B$1) Uses a date built with DATE() and a department name stored in B1, so a dropdown in B1 can switch which department the tile shows without rewriting the formula. Dates need to be serial numbers or DATE() values for locale safety.
Grand total headline number
=GETPIVOTDATA("Sum of Sales", A3) With no field and item pairs, it returns the grand total of the data field, handy for a single headline figure at the top of a report.
Watch out for
⚠
#REF! when the value isn't visible
GETPIVOTDATA returns a #REF! error when the field or item isn't currently shown in the pivot table, for example when a report filter hides it or a name is mistyped.
→ Check that the field and item are visible in the pivot table and that the names match exactly. Wrap the formula in IFERROR if you want a friendlier fallback.
⚠
Names have to match the pivot table
The data field is usually "Sum of Sales", not just "Sales", and item text is sensitive to spelling and spacing. A small mismatch breaks the formula.
→ Let Excel write the first formula for you (type "=" and click the value), then edit the field and item text from there.
⚠
Excel keeps writing GETPIVOTDATA
When you click a pivot table cell while building another formula, Excel inserts GETPIVOTDATA instead of a plain reference like B5.
→ Switch it off under PivotTable Analyze > Options > Generate GetPivotData, then click cells normally.
Tips & notes
GETPIVOTDATA accepts up to 126 field and item pairs, and the order of those pairs doesn't matter. If your reference covers more than one pivot table, it reads from the most recently created one, so point the second argument at a cell inside the table you actually want.
Common questions
Why does GETPIVOTDATA appear when I just want to click a cell?
Excel's "Generate GetPivotData" setting is on by default, so it assumes you want a stable reference into the pivot table. Turn it off under PivotTable Analyze > Options if you would rather get a normal cell reference like B5.
How do I stop the #REF! error?
It usually means the field or item isn't visible or a name is misspelled. Make sure the value shows in the pivot table and the text matches, then wrap it in IFERROR for a clean fallback.
Should I use GETPIVOTDATA or SUMIFS?
Use GETPIVOTDATA to read numbers that already exist in a pivot table. If you are working from a regular range or table, SUMIFS is usually simpler and more flexible.