GETPIVOTDATA function

Returns data stored in a PivotTable report. You can use GETPIVOTDATA to retrieve summary data from a PivotTable report, provided the summary data is visible in the report.

Syntax

GETPIVOTDATA(pivot_table,name)

Argument	Description	Remarks
pivot_table	A reference to a cell in the PivotTable report that contains the data you want to retrieve. The pivot_table argument can be a cell or range of cells in the report, a name for the range that contains the PivotTable report, or a label stored in a cell above the PivotTable report.	If pivot_table is a range that includes two or more PivotTable reports, data will be retrieved from whichever report was created most recently in the range. If pivot_table is not a range in which a PivotTable report is found, GETPIVOTDATA returns #REF! error value.
name	A text string enclosed in double quotation marks that describes the cell in the PivotTable report that contains the value you want to retrieve. For example, if the PivotTable report contains one row field labeled Salesperson and you want to retrieve the grand total value for a salesperson named Davolio, name would be "Davolio." If the column field in the PivotTable report contains products and you want the grand total value of beverage sales for a salesperson named Davolio, then name would be "Davolio Beverages."	If name describes a single cell, the value of that cell is returned regardless of whether it is a string, number, error, and so on. If the syntax of name does not describe a visible field, if name is omitted, or if name includes a page field that is not displayed, GETPIVOTDATA returns #REF! error value.

General remarks

Calculated fields or items and custom calculations are included in GETPIVOTDATA calculations.

Example

Assume the following PivotTable report is stored on a sheet, and the name PT1 refers to A2:E12, which is the range that contains the report.

Pivot Table report

A	B
Formula	Description (Result)
=GETPIVOTDATA(PT1,"Sum of Sales")	Returns the grand total of all data under the Sum of Sales field ($49,325)
=GETPIVOTDATA(PT1,"March")	Returns the grand total for March ($30,337)
=GETPIVOTDATA(A4,"March Buchanan Produce")	Returns Buchanan's produce sales total for March ($10,201)
=GETPIVOTDATA(A4,"March South")	Returns an error because the South region data is not visible (#REF!)
=GETPIVOTDATA(A4,"Davolio Beverages")	Returns an error because there is no total value of beverage sales for Davolio (#REF!)

See also
List of all functions (by category)

Share your feedback about this topic

Rate this content: