GETPIVOTDATA in Excel: Pull Numbers From a Pivot Table

GETPIVOTDATA in Excel pulls a single number out of a pivot table by naming it: "Sales for North, Laptop". Instead of pointing at a cell like B5, the formula describes the value, so it keeps working when the pivot is resized or rearranged. This guide is for anyone who builds reports or dashboards on top of pivot tables, or who has seen a long GETPIVOTDATA formula appear and wondered where it came from.

What does GETPIVOTDATA do?#

GETPIVOTDATA returns one value from a pivot table, chosen by field and item names. A field is a column from your source data, such as Region. An item is one value in that field, such as North.

It exists because pivot tables move. When you add a product, filter a region or refresh new data, the cell that held "North, Laptop" can shift to a different row. A plain reference like =B5 would then show the wrong number. GETPIVOTDATA finds the value by name, wherever it is.

You often meet it by accident. When you type = and click a cell inside a pivot table, Excel writes a GETPIVOTDATA formula for you instead of =B5.

GETPIVOTDATA syntax#

The function takes the value field, a cell in the pivot, and then pairs of field and item names:

=GETPIVOTDATA(data_field, pivot_table, [field1, item1], [field2, item2], ...)

You can add as many field and item pairs as the pivot has fields. With no pairs at all, GETPIVOTDATA returns the grand total. Field and item names are not case-sensitive.

Example: pull one total from a pivot table#

Here is a small sales list, the source data:

A pivot table on another sheet, starting at A3, has Region in Rows, Product in Columns and Sum of Sales in Values:

These formulas pull values from it:

If the formula sits on a different sheet from the pivot, the second argument includes the sheet name, such as Pivot!$A$3. The guide to referencing another sheet explains that syntax.

Make GETPIVOTDATA flexible with cell references#

Typed items like "North" lock the formula to one answer. Replace them with cells, and one formula can fill a whole report. Say F2:F4 hold East, North and South, and G1 holds Laptop. In G2:

=GETPIVOTDATA("Sales",Pivot!$A$3,"Region",$F2,"Product",G$1)

Fill it down and across. $F2 keeps the column fixed for regions, and G$1 keeps the row fixed for products. This is how many teams build a KPI summary: a neat, fixed layout that reads its numbers from a pivot underneath.

Dates need care. If a field holds real dates, give the item as a date value, such as DATE(2026,3,1), or as a cell that holds the date, not as typed text.

Turn off GETPIVOTDATA when you don't want it#

Sometimes you want a normal reference, for example to fill a formula down next to the pivot. GETPIVOTDATA doesn't adjust when filled, so every row would return the same value. There are two ways around it:

1. Type the address. Typing =B5 by hand, instead of clicking the cell, gives a normal reference.
2. Turn the feature off. On Windows, click inside the pivot, open the PivotTable Analyze tab, click the arrow next to Options and untick Generate GetPivotData. The same setting appears under File > Options > Formulas as "Use GetPivotData functions for PivotTable references". Excel for Mac has an equivalent setting, but its location depends on your version.

If you turn it off, remember that plain references can point at the wrong cell after the pivot changes shape.

Why GETPIVOTDATA returns #REF!#

GETPIVOTDATA returns #REF! when it can't find the value you described. The usual causes:

1. The item isn't visible. If North is filtered out, or the Region field was removed from the pivot, the value doesn't exist to return.
2. A name is misspelt. "Regoin" or "Nort" won't match. Copy names from the pivot's field list.
3. The wrong data field. Use the source field name that Excel writes, such as "Sales", and check it is in the Values area.
4. pivot_table points outside the pivot. The second argument must be a cell inside the pivot table.
5. The data changed and the pivot wasn't refreshed. A new region won't appear until you refresh (Windows: Alt+F5 for the active pivot). See how to refresh a pivot table.

Wrap the formula in IFERROR, such as =IFERROR(GETPIVOTDATA(...),0), only once you know the #REF! is expected, for example for a product with no sales yet.

GETPIVOTDATA or SUMIFS?#

Both give the same totals. The choice is about where the numbers come from:

• GETPIVOTDATA reads an existing pivot table. It's ideal when the pivot is already the agreed source, or when you need values shown as percentages from the pivot, such as percent of total.
• SUMIFS reads the source data directly. =SUMIFS(C2:C8,A2:A8,"North",B2:B8,"Laptop") returns the same 7,200 with no pivot and no refresh.

SUMIFS is also a good check: if it matches your GETPIVOTDATA result, your pivot is up to date. The beginner's guide to pivot tables shows how to build the pivot used in this guide.

Practise it#

GETPIVOTDATA only makes sense once you are comfortable with pivot tables themselves. Try the SUMIFS check on this page, then build your first pivot table in a live spreadsheet and go further with report filters and percent-of-total views. Each step is checked instantly, so you'll know your pivot is right before you pull numbers from it.