Tuesday 4 April 2017

Excel - Microsoft Excel GETPIVOTDATA Function

Description

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: 


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. 


- 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". 

No comments:

Post a Comment