The GETPIVOTDATA function returns data stored in a PivotTable report.

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

Syntax

=GETPIVOTDATA(data_field,pivot_table,[field1],[item1],[field2],[item2], … )

  • This function was introduced in Excel 2010 and so is not available in earlier versions.

Arguments

Argument Description
data_field The name, enclosed in quotation marks, for the data field that contains the data that you want to retrieve
pivot_table A reference to any cell, range of cells, or named range of cells in a PivotTable report
[field1],[item1],
[field2],[item2], …
Optional. Up to 126 pairs of field names and item names that describe the data that you want to retrieve. The item values should be entered as follows:

  numbers can be entered directly
  dates should be entered as date serial numbers or by using the DATE function
  times should be entered as decimals or by using the TIME function
  text values should be entered in quotations

Note: You can quickly enter a simple GETPIVOTDATA formula by typing = (the equal sign) in the cell you want to return the value to and then clicking the cell in the PivotTable report that contains the data you want to return.

Examples

  A B C D E    
1 Sum of Sales Operating System      
2 Quarter Salesperson Apple/Mac Window/PC Grand Total    
3 Jan-Mar John $35,220 $102,010 $137,230    
4   Sarah $87,250 $78,890 $166,140    
5 1st Qtr Total $122,470 $180,900 $303,370    
6 Apr-Jun John $55,940 $72,650 $128,590    
7   Sarah $54,610 $6,680 $61,290    
8 2nd Qtr Total $110,550 $79,330 $189,880    
9 Grand Total $233,020 $260,230 $493,250    
10            
11 Formula Result Notes
12 =GETPIVOTDATA(“Sales”,$A$4) $493,250 Returns the grand total of the Sales field
13 =GETPIVOTDATA(“Sales”,$A$4,”Qtr”,”1st”) $303,370 Returns the grand total for 1st Qtr
14 =GETPIVOTDATA(“Sales”,$A$4,”Qtr”,”1st”,”OpSys”,”PC”,”Salesperson”,”John”) $102,010  

Common Function Error(s)

Problem What went wrong
#REF! Occurs if either:

  the supplied pivot_table reference does not relate to a pivot table
  any of the fields specified by the data_field[field] or [item] arguments are not valid fields within the specified pivot table
  the field details are not displayed in the specified pivot table, e.g. total sales figures for SalesPerson ‘Abby’ are not displayed in the example spreadsheet above