What GETPIVOTDATA does
GETPIVOTDATA reads a figure from an actual Excel PivotTable. You identify the Values field and, if needed, the field items you want. That lets a separate report ask for West's revenue without depending on the worksheet cell where West's total happens to appear.
Practical examples
Retrieve the revenue grand total
Create a PivotTable from these source records:
| Region | Product | Revenue |
|---|---|---|
| East | Bags | 120 |
| East | Boxes | 80 |
| West | Bags | 75 |
| West | Boxes | 25 |
| North | Bags | 0 |
| North | Boxes | 0 |
Place the actual PivotTable at A4 on a sheet named Pivot, with Region in Rows, Product in Columns, and Revenue in Values summarized by Sum. Keep all items visible and grand totals enabled. Use the Values caption Sum of Revenue and confirm that Pivot!A4 is inside the PivotTable.
On a separate sheet named Report, enter:
=GETPIVOTDATA("Sum of Revenue",Pivot!$A$4)FormulaThe expected result is 300, from 120 + 80 + 75 + 25 + 0 + 0. With no field/item pairs, the formula requests the grand total for that Values field.
Read the region from a report cell
Keep the same unfiltered PivotTable and enter West in Report!B4. In Report!B6, enter:
=GETPIVOTDATA("Sum of Revenue",Pivot!$A$4,"Region",B4)FormulaThe expected result is 100, West's 75 for Bags plus 25 for Boxes. Change B4 to East and the expected result becomes 200. Change it to North and the expected result is 0, because both North records have zero revenue.
"Region" identifies the field; B4 supplies its item. Keep B4 outside quotation marks so Excel reads the cell instead of searching for an item literally named B4. The fixed-report tutorial develops this example further.
Common mistakes and notes
The anchor identifies a PivotTable, not the answer cell
Pivot!$A$4 only needs to stay inside the intended PivotTable. A range of typed totals or a formula-generated summary isn't a PivotTable. Avoid a reference spanning several PivotTables: Microsoft notes that it can select the one created most recently.
Requested data must be available
An invalid PivotTable reference or a request for unavailable fields or items can return #REF!. For example, if West is filtered out, a request for West cannot retrieve its visible figure. That error is different from North's genuine zero. Check filters, field names, item names, and the anchor before replacing an error.
Match the Values field and keep pairs together
Use a valid Values caption, such as "Sum of Revenue", or the documented underlying field name, "Revenue". Each optional field must have its corresponding item. Text names can be quoted or read from cells; for date items, use an Excel date serial or a date-producing formula rather than ambiguous date text.
GETPIVOTDATA reads the PivotTable's current figures
It doesn't refresh the PivotTable for you. When source records change, make sure the PivotTable includes them and is refreshed before relying on the retrieved result. Rearranging its layout can leave the request intact, but removing required fields or hiding items changes what is available.