The Excel GETPIVOTDATA Function

The Excel GETPIVOTDATA function extracts data from an Excel Pivot Table

The format of the function is :

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

where the arguments are as follows:

data_field - The pivot table data field (ie. the value(s) in the center of the table) that you want to retrieve
pivot_table - A reference to a range of cells within a Pivot Table (used to specify the pivot table to be searched)
[field1], [item1],
[field2], [item2],
etc
-

Up to 126 optional pairs of fields and item names, (ie. The rows and columns headers and individual categories) for which you want the returned value.

Note: 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 that, if the requested fields are not visible in the specified Pivot Table, the Getpivotdata function returns the #REF! error.

The easiest way to input the Getpivotdata function is simply to type "=" into a cell and then click on the Pivot Table value that you want to return. Excel automatically inserts the Getpivotdata function into the active cell.


Getpivotdata Function Examples

The examples below all refer to the following Pivot Table, which is located in columns A - G of the current Excel Worksheet.

  A B C D E F G  
1                
2 Sum of Invoice Amount Total Item Type    
3 Date Salesperson Digital
TV
DVD
Player
Ipod Computer Grand Total  
4 Jan John $68,600 $13,800 $6,840 $69,600 $158,840  
5 Kevin $64,400 $7,800 $12,780 $36,800 $121,780  
6 Pete $46,200 $2,400 $2,700 $25,600 $76,900  
7 Jan Total $179,200 $24,000 $22,320 $132,000 $357,520  
8 Feb John $68,600 $8,400 $9,720 $52,800 $139,520  
9 Kevin $61,600 $4,500 $7,920 $43,200 $117,220  
10 Pete $29,400 $3,900 $6,300 $33,600 $73,200  
11 Feb Total $159,600 $16,800 $23,940 $129,600 $329,940    
12 Mar John $71,400 $9,300 $7,560 $80,000 $168,260  
13 Kevin $70,000 $10,200 $13,680 $58,400 $152,280  
14 Pete $43,400 $5,400 $3,240 $40,800 $92,840  
15 Mar Total $184,800 $24,900 $24,480 $179,200 $413,380  
16 Grand Total $523,600 $65,700 $70,740 $440,800 $1,100,840  
17                


=GETPIVOTDATA( "Invoice Amount", $A$2, "Date", "Jan" )

- Returns the value $357,520, which is the value of the Invoice Amount Total for all of the Date field, "Jan".


=GETPIVOTDATA( "Invoice Amount", $A$2, "Date", "Feb", "Item Details", "IPod" )

- Returns the value $23,940, which is the value of the Invoice Amount Total for the Date field, "Feb" and the Item Details field, "IPod".


=GETPIVOTDATA( "Invoice Amount", $A$2, "Date", "Feb", "Item Details", "IPod", "Salesperson", "Kevin" )

- Returns the value $7,920, which is the value of the Invoice Amount Total for the Date field, "Feb", the Item Details field, "IPod", and the Salesperson field "Kevin".


=GETPIVOTDATA( "Invoice Amount", $A$2, "Item Details", "IPod", "Salesperson", "Kevin" )

- Returns the Excel #REF! error as the Pivot Table doesn't show the totals for the Salesperson "Kevin".

Return to the Excel Built-In Functions page

Valid XHTML 1.0 Transitional Valid CSS!
Disclaimer   Privacy Policy
Copyright © 2008-2014 ExcelFunctions.net