Back to functions
Lookup & Reference2026-11-161 related article

GETPIVOTDATA Function in Excel

Retrieve a visible PivotTable value by its data field and optional field and item pairs.

Syntax

GETPIVOTDATA(data_field, pivot_table, [field1, item1, field2, item2], ...)
Table of Contents

Arguments

data_field

Required

The Values field to retrieve, supplied as a quoted field caption or its underlying field name, or a reference containing that text.

pivot_table

Required

A cell, range, or named range identifying the actual PivotTable. One cell inside the intended PivotTable is usually the clearest choice.

field1, item1

Optional

The first field and item pair selecting the visible figure to retrieve, such as Region and West.

field2, item2, ...

Optional

Additional field and item pairs, in any order, up to 126 pairs in total.

What it returns

The requested visible PivotTable value, or its Values field grand total when no field and item pairs are supplied.

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:

RegionProductRevenue
EastBags120
EastBoxes80
WestBags75
WestBoxes25
NorthBags0
NorthBoxes0

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)
Formula

The 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)
Formula

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

Related functions

Related articles

Deep dives, troubleshooting guides, and practical examples that use GETPIVOTDATA.

Official documentation