ExcelFlux

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

RowAB
6RegionAmount
7North12
8South8
9East5
Record 1
=GETPIVOTDATA("Amount",$J$3,"Region",A7)

Illustrative output12

See the inputs and instructions for all 3 examples

Worked 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.

Microsoft Excel reference