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.
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).
The name of the data_field, and field/item values must be enclosed in double quotes.
GETPIVOTDATA will return a #REF error if any fields are spelled incorrectly.
To get the subtotal for a value field in a pivot table that has been grouped by date, you can use the GETPIVOTDATA function and a number that corresponds to the date grouping. In the example shown, the formula in I7 is...
To get the grand total for a value field in a pivot table, you can use the GETPIVOTDATA function. In the example shown, the formula in I6 is:
Although you can reference any cell...
Formulas are the key to getting things done in Excel. In this accelerated 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. See details here.