How can I prevent Excel from automatically inserting GETPIVOTDATA when I click a PivotTable cell?

Go to File → Options → Formulas and uncheck “Use GETPIVOTDATA functions for PivotTable references”. This stops Excel from generating the function and lets you use ordinary cell references instead.

Can GETPIVOTDATA retrieve values from calculated fields in a PivotTable?

Yes. As long as the calculated field appears in the Values area and has a caption, you can pass that caption as the data_field argument and GETPIVOTDATA will return the computed result.

Why does GETPIVOTDATA sometimes return 0 instead of a blank cell for a missing item?

GETPIVOTDATA returns the underlying value stored in the PivotCache. If the item does not exist, the cache records a zero for numeric fields. Use IFERROR or a custom check on the source data if you need a truly blank result.

Is GETPIVOTDATA available in Google Sheets and does it work the same way as in Excel?

Google Sheets supports GETPIVOTDATA with the same syntax, but it only works on PivotTables created in Sheets. Some edge-case behaviors—such as automatic field name generation—may differ, so always test formulas after migrating.

GETPIVOTDATA function

GETPIVOTDATA extracts a single value from a PivotTable by specifying the data field and optional field/item filters, returning the exact cell content.

=GETPIVOTDATA(data_field, pivot_table, [field1], [item1])

Generate a GETPIVOTDATA formula

Describe what you need. The generator will reach for GETPIVOTDATA where GETPIVOTDATA is the right tool, and tell you when it is not.

Arguments

How GETPIVOTDATA reads its arguments

Argument	Required	Description
data_field	Required	String – the exact caption of the value field in the PivotTable (e.g., "Spend").
pivot_table	Required	Reference – a cell inside the PivotTable that anchors the lookup.
field1	Optional	Optional string – the name of a PivotTable row or column field to filter (e.g., "Campaign").
item1	Optional	Optional string – the specific item within field1 to match (e.g., "Spring Sale").

Returns

It returns a scalar value (number, text, or error) that matches the requested PivotTable intersection.

Availability

Excel: All · Google Sheets: Supported

Worked examples

1. Total spend for a single campaign

Campaign	Email	Social	Paid Search
Spring Sale	5000	3000
Summer Blast	7000		4000

=GETPIVOTDATA("Spend",$A$3,"Campaign","Spring Sale")

Result: 8000

The PivotTable sums Spend for each campaign across all channels. For "Spring Sale" the Email spend is 5,000 and Social spend is 3,000, giving a total of 8,000. The formula points to cell A3 inside the PivotTable, requests the "Spend" field, and filters on the Campaign "Spring Sale", so it returns that summed total.

2. Conversions for a campaign-channel pair

Campaign	Email	Social	Paid Search
Summer Blast			55
Spring Sale		30

=GETPIVOTDATA("Conversions",$A$3,"Campaign","Summer Blast","Channel","Paid Search")

Result: 55

The PivotTable also contains a "Conversions" value field. For "Summer Blast" on the "Paid Search" channel the underlying data shows 55 conversions. By specifying both the Campaign and Channel items, GETPIVOTDATA pinpoints that exact cell and returns 55.

3. Overall clicks for a specific channel

Channel	Email	Social	Paid Search
Clicks	1200	800
	1500		1100

=GETPIVOTDATA("Clicks",$A$3,"Channel","Email")

Result: 2700

When only a column field is supplied, GETPIVOTDATA aggregates over all row items. The Email column shows 1,200 clicks for "Spring Sale" and 1,500 clicks for "Summer Blast". Adding those gives 2,700, which is the value the formula returns.

Common errors

Which GETPIVOTDATA error are you seeing?

Error	Why it happens	How to fix it
#REF!	The pivot_table argument points to a cell that is not inside any PivotTable.	Change the reference so it points to a cell that belongs to the desired PivotTable, such as the top-left data cell.
#VALUE!	The data_field string does not match any value field caption in the PivotTable (e.g., misspelling "Spnd").	Use the exact field name as displayed in the PivotTable field list, respecting case and spaces.
#N/A	A specified field/item combination does not exist in the PivotTable, such as "Campaign","Winter Promo".	Verify that the item name exists in the source data and appears in the PivotTable, then correct the string.

Tips and when to use something else

Use GETPIVOTDATA when you need values that stay correct even after the PivotTable layout changes.
Turn off Excel's automatic GETPIVOTDATA generation (File → Options → Formulas) if you prefer plain cell references.
Wrap the call in IFERROR to provide a friendly fallback when the requested item is missing.
For simple conditional totals on the raw data, SUMIFS is usually faster and does not require a PivotTable.

Related functions

VLOOKUPVLOOKUP searches the first column of a table and returns a value from a specified column in the same row, optionally using exact or approximate matching.XLOOKUPXLOOKUP returns the value from a specified return array that corresponds to a given lookup value, with optional handling for missing matches and search direction.INDEXINDEX returns the value or array of values from a specified position within a given range, based on row and optional column numbers.MATCHMATCH returns the relative position of a lookup value within a one-dimensional range, optionally using exact, less-than, or greater-than matching.OFFSETOFFSET returns a reference to a range that is a specified number of rows and columns from a starting cell, optionally sized by height and width.

Frequently asked questions

How can I prevent Excel from automatically inserting GETPIVOTDATA when I click a PivotTable cell?: Go to File → Options → Formulas and uncheck “Use GETPIVOTDATA functions for PivotTable references”. This stops Excel from generating the function and lets you use ordinary cell references instead.
Can GETPIVOTDATA retrieve values from calculated fields in a PivotTable?: Yes. As long as the calculated field appears in the Values area and has a caption, you can pass that caption as the data_field argument and GETPIVOTDATA will return the computed result.
Why does GETPIVOTDATA sometimes return 0 instead of a blank cell for a missing item?: GETPIVOTDATA returns the underlying value stored in the PivotCache. If the item does not exist, the cache records a zero for numeric fields. Use IFERROR or a custom check on the source data if you need a truly blank result.
Is GETPIVOTDATA available in Google Sheets and does it work the same way as in Excel?: Google Sheets supports GETPIVOTDATA with the same syntax, but it only works on PivotTables created in Sheets. Some edge-case behaviors—such as automatic field name generation—may differ, so always test formulas after migrating.

Need a different formula?

The full generator is not scoped to one function — describe any spreadsheet problem and it will pick.

Open the formula generator

Reviewed 2026-09-17