GETPIVOTDATA

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

Syntax

GETPIVOTDATA(pivot_table,name)

Pivot_table is the name of the PivotTable that contains the data you want to retrieve. Pivot_table can be a cell or range of cells in the PivotTable, a name for the range that contains the PivotTable, or a label stored in a cell above the PivotTable.

Name is a text string enclosed in double quotation marks that describes the cell in the PivotTable that contains the value you want to retrieve. For example, if the PivotTable contains one row field labeled Salesperson and you want to retrieve the grand total value for a salesperson named Suyama, name would be "Suyama." If the column field in the PivotTable contains products and you want the total grand total value of beverage sales for a salesperson named Suyama, then name would be "Suyama Beverages."

Remarks

Calculated fields or items and custom calculations are included in GETPIVOTDATA calculations.
If pivot_table is a range that includes two or more PivotTables, data will be retrieved from whichever PivotTable was created most recently in the range.
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 pivot_table is not a range in which a PivotTable is found, GETPIVOTDATA returns #VALUE!.
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!.

Examples

Assuming the following PivotTable is stored on a worksheet, and the name PT1 refers to A2:E12, the range that contains the PivotTable is:

	A	B	C	D	E
2	Region	North
3
4	Sum of Sales		Product
5	Month	Salesperson	Beverages	Produce	Grand Total
6	March	Buchanan	$3,522	$10,201	$13,723
7		Davolio	$8,725	$7,889	$16,614
8	March Total		$12,247		$18,090	$30,337
9	April	Buchanan	$5,594	$7,265	$12,859
10		Davolio	$5,461	$668	$6,129
11	April Total		$11,055		$7,933	$18,988
12	Grand Total		$23,302		$26,023	$49,325

GETPIVOTDATA(PT1,"Sum of Sales") returns the grand total of the Sales field, $49,325.

GETPIVOTDATA(PT1,"March") returns the grand total for March, $30,337.

The following examples refer to the same PivotTable above but use a cell within the PivotTable for the pivot_table argument:

GETPIVOTDATA(A4,"March Buchanan Produce") returns $10,201.

GETPIVOTDATA(A4,"March South") returns #REF! because the South region data is not visible.

GETPIVOTDATA(A4,"Davolio Beverages") returns #REF!; there is no total value of beverage sales for Davolio.