GETPIVOTDATA function
data stored in a PivotTable report.
Lookup and reference · Guided Excel example
Where can you use it?
Reporting and operations teams. Use functions in this group to retrieve a field from an item, employee or order list, select the right record, or move values into another report.
Scenario in the workbook
Insert a PivotTable from A6:B9 at J3. Use Region in Rows and Amount in Values. Match the value-field caption in the formula.
A worked example from the workbook
| Row | A | B |
|---|---|---|
| 6 | Region | Amount |
| 7 | North | 12 |
| 8 | South | 8 |
| 9 | East | 5 |
=GETPIVOTDATA("Amount",$J$3,"Region",A7)Illustrative output12
See the inputs and instructions for all 3 examplesWorked examples
Insert a PivotTable from A6:B9 at J3. Use Region in Rows and Amount in Values. Match the value-field caption in the formula.
Column F provides expressions as text. Satisfy the stated requirements, then paste into an empty Excel cell and compare with column G. The result was not verified in desktop Excel here.
| Row | A | B |
|---|---|---|
| 6 | Region | Amount |
| 7 | North | 12 |
| 8 | South | 8 |
| 9 | East | 5 |
| Trial | Expression | Sample output |
|---|---|---|
| Record 1 | =GETPIVOTDATA("Amount",$J$3,"Region",A7) |
12 |
| Record 2 | =GETPIVOTDATA("Amount",$J$3,"Region",A8) |
8 |
| Record 3 | =GETPIVOTDATA("Amount",$J$3,"Region",A9) |
5 |
If the result is missing
J3 must be inside the PivotTable. Check filtered-out regions and mismatched field captions.