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. The GETPIVOTDATA function is generated automatically when you reference a value cell in a pivot table by pointing and clicking. 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 GETPIVOTDATA” in the menu at Pivot TableTools > Options > Options (far left, below the pivot table name).
Examples
The examples below are based on the following pivot table:
The first argument in the GETPIVOTDATA function names the field from which to retrieve data. The second argument is a reference to a cell that is part of the pivot table. To get total Sales from the pivot table shown: Fields and item pairs are supplied in pairs entered as text values. To get total sales for the Product Hazelnut: To get total Sales for the West region: To get total sales for Almond in the East region, you can use either of the formulas below: You can also use cell references to provide field and item names. In the example shown above, the formula in I8 is: The values for Region and Product come from cells I5 and I6. The data is collected based on the region “Midwest” in cell I6 for the product “Hazelnut” in cell I7.
Dates and times
When using GETPIVOTDATA to fetch information from a pivot table based on a date or time date or time, use Excel’s native format, or a function like the DATE function. For example, to get total Sales on April 1, 2021 when individual dates are displayed: When dates are grouped, refer to the group names as text. For example, if the Date field is grouped by year:
Notes
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. GETPIVOTDATA will return a #REF error if the reference to pivot_table is not valid.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
