Excel Pivot Tables are used to group tables of data stored in Excel spreadsheets.
They are an excellent tool for performing quick and easy analysis of large amounts of data.
If you want to learn more about pivot tables in Excel, see our Excel Pivot Table Tutorial or the more concise page on creating Excel Pivot Tables.The Excel Getpivotdata function extracts data from specified fields of an Excel Pivot Table.
The syntax of the function is:
where the function arguments are:
data_field    The pivot table data field (i.e. the data header in your original data set) that you want to retrieve. 
pivot_table   
A reference to any cells or range of cells within the Pivot Table to be searched. (This reference is simply used to specify the pivot table). 
[field1], [item1], [field2], [item2], etc 
 
Up to 126 optional pairs of fields and item names, (i.e. The row and column headers and individual categories) for which you want the returned value. Note: The item values should be entered as follows:

Note that, if the requested fields are not visible in the specified Pivot Table, the Getpivotdata function returns the #REF! error.
If the 'Use GetPivotData functions for PivotTable references' Excel option is enabled, 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 then automatically inserts the Getpivotdata function into the active cell.
(Note: the 'Use GetPivotData functions for PivotTable references' option is located in the 'Formulas' section of the 'Excel Options' dialog box, which is accessed from the 'File' tab of Excel).
The examples below all refer to the following Pivot Table, which is located in columns AG 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 
Note that although the above examples all use the reference $A$2 as the pivot_table argument, this argument is simply used to specify the pivot table and so could actually be any cell or cell range within the pivot table.
For further information on the Getpivotdata Function, see the Microsoft Office website.
If you get an error from the Excel Getpivotdata function, this is likely to be the #REF! error:
#REF!   
Occurs if either:
