Excel Pivot Tables are used to group sets 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 value(s) in the center of the table) that you want to retrieve.|
|pivot_table||-||(This reference is simply used to specify the pivot table).|
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.
The examples below all refer to the following Pivot Table, which is located in columns A - G of the current Excel Worksheet.
|2||Sum of Invoice Amount Total||Item Type|
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 a reference to any cell or cell range within the pivot table.
Further information on the Getpivotdata Function is provided on the Microsoft Office website.
If you get an error from the Excel Getpivotdata function, this is likely to be the #REF! error: