GETPIVOTDATA in Excel: Syntax, Examples and Errors

Part of the free Module 10: Pivot Tables · Lesson 17 of 20 · Full Excel course

Updated on 5 September 2026 · Works in Excel 365, 2021, 2019 and 2016 unless noted.

GETPIVOTDATA is an Excel function that pulls a specific value out of a pivot table by naming the field and item you want, instead of pointing at a cell address. Because it looks up the value by name, the formula keeps returning the right number even when the pivot table is re-sorted, filtered, refreshed or grows by a few rows. That makes it the safest way to feed pivot results into a dashboard or report.

Why GETPIVOTDATA beats a direct cell reference

Suppose your pivot shows total revenue for Neha in cell C7 and you write =C7 in a summary sheet. Tomorrow you sort the pivot by revenue, a new supervisor joins the data, or you add a filter. Neha’s total is now in C9, and your summary silently shows someone else’s number. A direct reference points at a position, and the position is not stable in a pivot table.

=GETPIVOTDATA("Revenue",$A$3,"Supervisor","Neha") points at a meaning. Wherever Neha’s total sits after the next refresh, the function finds it. If the item disappears from the report, you get #REF! rather than a wrong figure, which is exactly what you want a report to do.

GETPIVOTDATA syntax and arguments

The full syntax is =GETPIVOTDATA(data_field, pivot_table, [field1, item1], [field2, item2], ...). Field and item arguments always come in pairs, and you can add up to 126 pairs.

Argument	Required	Meaning	Example
`data_field`	Yes	Name of the value field you want, in quotes. Use the source column name (“Revenue”). The caption “Sum of Revenue” also works in current Excel, but the source name is the safer choice.	`"Revenue"`
`pivot_table`	Yes	Any cell inside the pivot table. Excel uses it only to identify which pivot to read. The top-left cell is the usual choice.	`$A$3`
`field1`	No	Name of a row, column, filter or slicer field, in quotes.	`"Supervisor"`
`item1`	No	The item inside that field whose value you want. Text goes in quotes, numbers do not, dates must be real dates.	`"Neha"`
`field2, item2 ...`	No	Further pairs narrow the result to an intersection, for example one supervisor and one product.	`"Product","Laptop"`

With no field and item pairs at all, the function returns the grand total of the data field.

How Excel writes GETPIVOTDATA for you

You rarely need to type the function from scratch. Excel builds it automatically when you reference a pivot cell from a formula.

Click the cell where the result should appear (on the same sheet or a different one).
Type =.
Click any value cell inside the pivot table.
Press Enter. Excel inserts a complete GETPIVOTDATA formula with every field and item hard-coded.

The generated formula looks like =GETPIVOTDATA("Revenue",$A$3,"Supervisor","Amit","Product","Laptop"). Edit the quoted item names or replace them with cell references to make it flexible.

Turn Generate GetPivotData on or off

Some people find the automatic formula annoying when they only want a plain reference. You can switch the behaviour either way.

Click inside the pivot table so the PivotTable Analyze tab appears.
In the PivotTable group, open the Options drop-down arrow.
Tick or untick Generate GetPivotData.

The same setting lives under File > Options > Formulas > Working with formulas > Use GetPivotData functions for PivotTable references. When it is off, clicking a pivot cell inserts a normal reference such as =C7. You can still type GETPIVOTDATA by hand at any time.

Worked example: supervisor revenue by product

Open the course practice file, insert a pivot table on a new sheet starting at cell A3, put Supervisor in Rows, Product in Columns and Revenue in Values. The report looks like this.

Sum of Revenue	Desktop	Laptop	Tablet	Grand Total
Amit	42,500	61,200	18,300	122,000
Neha	38,900	55,700	22,100	116,700
Rahul	29,400	47,800	15,600	92,800
Grand Total	110,800	164,700	56,000	331,500

Now write these formulas anywhere in the workbook. Each one reads the pivot by name.

What you want	Formula	Result
Grand total of all revenue	`=GETPIVOTDATA("Revenue",$A$3)`	331,500
Total for one supervisor	`=GETPIVOTDATA("Revenue",$A$3,"Supervisor","Neha")`	116,700
Total for one product	`=GETPIVOTDATA("Revenue",$A$3,"Product","Tablet")`	56,000
One intersection	`=GETPIVOTDATA("Revenue",$A$3,"Supervisor","Amit","Product","Laptop")`	61,200
Dynamic version (names in H2 and H3)	`=GETPIVOTDATA("Revenue",$A$3,"Supervisor",H2,"Product",H3)`	Depends on H2 and H3

For the dynamic version, type Rahul in H2 and Desktop in H3 and the formula returns 29,400. Change H3 to Laptop and it returns 47,800 without touching the formula.

Passing a date item

If the Date field is in the pivot and is not grouped, the item must be a real date, not text. Use DATE() or a cell that contains a date:

=GETPIVOTDATA("Revenue",$A$3,"Date",DATE(2024,3,15))
=GETPIVOTDATA("Revenue",$A$3,"Date",H4)

If you have grouped the dates into months, quarters or years, refer to the group field and the label exactly as the pivot shows it, for example "Qtr1" or "Mar". The name of the group field differs between Excel versions (“Quarters” in older builds, “Date (Quarter)” style in some Excel 365 builds), so the reliable route is to let Excel generate the formula once by clicking the cell, then copy the field name it used.

Make GETPIVOTDATA dynamic for dashboards

Hard-coded item names are fine for a fixed report. For a dashboard, drive the items from cells so a user can change the view without editing formulas.

Create a small input area, for example H2 for the supervisor and H3 for the product.
Add a drop-down to each input with Data > Data Validation > List, pointing at the list of supervisors and products.
Write the formula with cell references: =GETPIVOTDATA("Revenue",$A$3,"Supervisor",$H$2,"Product",$H$3).
Wrap it in IFERROR so a combination with no sales shows 0 instead of #REF!: =IFERROR(GETPIVOTDATA("Revenue",$A$3,"Supervisor",$H$2,"Product",$H$3),0).
Point charts, KPI cards and conditional formatting at these formula cells, never at the pivot itself.

Two details matter here. First, the field names (“Supervisor”, “Product”) must match the pivot’s field names exactly, including spaces. Second, the data field should be the underlying column name. Both "Revenue" and "Sum of Revenue" work in Excel 365 and 2021, but "Revenue" keeps working if someone later changes the summary to Average or renames the caption.

Show Values As: GETPIVOTDATA returns what you see

GETPIVOTDATA reads the number that is displayed in the pivot cell, not the raw source data. If you have set the value field to Show Values As > % of Grand Total, the function returns the percentage (as a decimal such as 0.35), not the revenue. The same applies to running totals, differences from a base item and ranks. If you need both the amount and the percentage on a dashboard, add the Revenue field to Values twice with different settings and read each one by its own data field name.

Errors and how to fix them

Symptom	Cause	Fix
`#REF!`	The item is not visible in the pivot: it is filtered out, hidden by a slicer, collapsed under a parent, or does not exist in the data.	Clear the filter or slicer, expand the group, or wrap the formula in IFERROR to show 0.
`#REF!` for every formula	A field name is misspelt, or the data field caption was renamed and the old caption is quoted.	Check spelling and spaces; use the source column name for the data field.
`#REF!` on a date	The date was passed as text (“15/03/2024”) instead of a real date.	Use `DATE(2024,3,15)` or reference a cell that holds a true date.
Result is right, then wrong after refresh	The `pivot_table` reference points at a cell that is no longer inside the pivot after it shrank.	Reference the top-left cell of the pivot, which never moves.
Result changes unexpectedly	A report filter or slicer is applied, so the pivot (and therefore the function) shows a subset.	Either include the filter field as a field and item pair, or remove the filter.
Value shows as text or 0	The item argument is a number stored as text, or the formula cell is formatted as Text.	Match the item’s data type to the pivot (number vs text) and set the cell format to General.

Tips and common mistakes

Anchor the pivot reference. Use $A$3 so the reference does not shift when you copy the formula down a summary table.
Let Excel write the first formula. Click a pivot cell, then edit. This avoids typos in field names and shows you the exact grouped field name.
Use the source column name for data_field. “Revenue” survives caption changes; “Sum of Revenue” does not.
Keep the pivot fields you query in the layout. If you remove Product from the pivot, every formula that names “Product” returns #REF!.
Filters change the answer. GETPIVOTDATA returns the filtered figure. State the filter in the formula if the report must be explicit.
Refresh before you trust the number. The function reads the pivot cache, not the source sheet. Press Alt + F5 to refresh after the data changes.
Do not mix data types. A numeric item like an invoice number must be passed as a number, not “1001” in quotes.

Practice exercise

Use the course practice file and build the Supervisor by Product pivot at A3 on a new sheet.

Write a formula that returns the grand total revenue, then sort the pivot by revenue descending and confirm the formula still returns the same number.
Return the Laptop revenue for the supervisor whose name you type into cell H2. Add a data validation list to H2 so the name can be picked.
Wrap the formula from task 2 in IFERROR so a supervisor with no laptop sales shows 0.
Change the value field to Show Values As > % of Row Total and observe what the same formula now returns. Change it back to No Calculation.
Apply a report filter on Date to a single month and note how the grand total formula changes. Remove the filter.

Key takeaways

GETPIVOTDATA fetches a pivot value by field and item name, so it survives sorting, filtering and refreshes that break ordinary cell references.
Excel writes the formula automatically when you type = and click a pivot cell; the Generate GetPivotData option controls this.
Replace quoted item names with cell references and add IFERROR to build dashboard-ready formulas.
Dates must be real dates (DATE or a date cell); grouped fields use the group field name and the label shown in the pivot.
The function returns the displayed value, so Show Values As settings and filters change the answer.

Related lessons

Frequently asked questions

How do I stop Excel from using GETPIVOTDATA?

Click inside the pivot table, open the PivotTable Analyze tab, click the Options drop-down arrow in the PivotTable group and untick Generate GetPivotData. Excel will then insert plain cell references such as =C7 when you click a pivot cell. The same switch is in File > Options > Formulas.

Why does GETPIVOTDATA return #REF!?

The most common reason is that the item you named is not currently visible in the pivot table: it has been filtered out, hidden by a slicer, or collapsed inside a group. A misspelt field name, a renamed data field caption or a date passed as text also causes #REF!. Wrap the formula in IFERROR if a blank or 0 is acceptable.

Can GETPIVOTDATA use a cell reference instead of a typed item name?

Yes. Replace the quoted item with a cell address, for example =GETPIVOTDATA("Revenue",$A$3,"Supervisor",H2). Whatever name is typed or selected in H2 is looked up. This is how dashboard drop-downs are connected to pivot data, and it works with data validation lists and slicer-driven inputs.

Does GETPIVOTDATA work with dates and grouped dates?

It does, but the item must be a real date value, so use DATE(2024,3,15) or a cell containing a date rather than typing “15/03/2024”. When dates are grouped into months, quarters or years, refer to the group field and use the label exactly as the pivot displays it, such as “Qtr1” or “Mar”.

Is GETPIVOTDATA slow in large workbooks?

It is a lightweight lookup against the pivot cache and is usually faster than SUMIFS over the raw data, because the pivot has already done the aggregation. Thousands of GETPIVOTDATA formulas are fine. The only cost is that the pivot must be refreshed for the formulas to reflect new source data.

Want the finished version? Ready-made Excel dashboards, trackers and VBA systems are available at NextGenTemplates.com.