📎 This article includes 1 downloadable practice file ↓
In this article
GETPIVOTDATA pulls a value from a pivot by field names, not by cell position — so your report keeps working when the pivot grows or reorders. Excel writes it for you when you click a pivot cell.
Syntax
=GETPIVOTDATA(data_field, pivot_table, [field1, item1], ...)| Argument | What it means |
|---|---|
pivot_table |
Any cell in the pivot. |
Examples
Example 1
=GETPIVOTDATA("Amount", $A$3, "Region", B1, "Month", C1)Region and month taken from cells, so one formula fills a whole grid.
GETPIVOTDATA in practice
Where you will use it
- Pull a PivotTable value into a report that won't break
- Dashboards from pivots
- Stable references when the pivot changes shape
Worked example
| Region | Formula | Result |
|---|---|---|
| North | =GETPIVOTDATA("Sales",$H$3,"Region",A2) | (North total from the pivot) |
| South | =GETPIVOTDATA("Sales",$H$3,"Region",A3) | (South total from the pivot) |
| West | =GETPIVOTDATA("Sales",$H$3,"Region",A4) | (West total from the pivot) |
Needs a PivotTable; not calculated here.
Mistakes people make
| Mistake | What to do instead |
|---|---|
| Turning it off | File > Options > Formulas > Use GetPivotData functions |
| #REF! | The item isn't visible in the pivot |
Related functions
📚 Part of the free Excel course: Beginner → Expert · Try it in the Formula Lab or ask the AI Helper.
Test formulas dynamically in our web formula laboratory or download the companion Excel workbook.
📎 Practice files for this article
- 📗Practice workbook: Lookup & reference17 tasks on realistic sales data — type your formula, the Check column turns green when it is right. Answers sheet included.⬇ XLSX · 35 KB
Free to use for learning. Files with macros (.bas) are plain text — import them with Alt+F11 → File → Import File, and always test on a copy.
Stuck on a step? Ask a question and the AI answers using this article.