Quick, clean, and to the point


Excel GETPIVOTDATA function

The Excel GETPIVOTDATA function can query a pivot table and retrieve specific data based on the pivot table structure, instead of cell references.

Retrieve data from a pivot table in a formula
Return value 
The data requested
=GETPIVOTDATA (data_field, pivot_table, [field1, item1], ...)
  • data_field - The name of the value field to query.
  • pivot_table - A reference to any cell in the pivot table to query.
  • field1, item1 - [optional] A field/item pair.
Usage notes 

Use the GETPIVOTDATA function to query an existing pivot table and retrieve specific data based on the pivot table structure. The first argument (data_field) names a value field to query. The second argument (pivot table) is a reference to any cell in an existing pivot table.

Additional arguments are supplied in field/item pairs that act like filters to limit the data retrieved based on the structure of the pivot table. For example, you might supply the field "Region" with the item "East" to limit sales data to sales in the East region.

For example, in the screen on this page,the formula in cell I7 is:


This causes the GETPIVOTABLE function to retrieve data from the field "Sales" in the pivot table that begins in cell B3. The data is limited to the region "Midwest" for the product "Hazelnut". The values for Region and Product come from cells I5 and I6.

The GETPIVOTABLE function is generated automatically when you reference a value cell in a pivot table. To avoid this, you can simply type the address of the cell you want (instead of clicking). If you want to disable this feature entirely, disable "Generate GETPIVOTABLE" in the menu at Pivot TableTools > Options > Options (far left, below the pivot table name).


  1. The name of the data_field, and field/item values must be enclosed in double quotes.
  2. GETPIVOTDATA will return a #REF error if any fields are spelled incorrectly.

Excel Formula Training

Make sure you have a solid foundation in Excel formulas! In this step-by-step training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access so you can start today. See details here.