Sunday, 13 January 2013

Excel GETPIVOTDATA Function Syntax and Example

Excel GETPIVOTDATA Function
If you want to use values from the pivot table elsewhere in the workbook, you can enter the appropriate cell references directly into the formulas. However, as the pivot table changes, these references might not refer to the correct values you want to use in the formula.

To solve this problem, Excel has a function called GETPIVOTDATA, which references values in the pivot table by their logical positions, rather than actual cell addresses. Even when the table is pivoted, the function still returns the correct value. 

However, if the referenced value is removed or hidden (such as by filtering or by hiding details), the function returns a #REF error.

When you are creating a formula and you click on a cell in a pivot table, Excel automatically inserts the appropriate GETPIVOTDATA function into the formula to refer to the value you selected.

Syntax:
GETPIVOTDATA(data_field,pivot_table,field1,item1,field2,item2,...)

Data_field is the name of the field in the data area from which we want to retrieve a value.
Pivot_table is any cell in the pivot table, usually the upper-left corner.
Fieldn/Itemn list the name of the column/row field and the specific item value of that field for the table value we want to return. This combination of field/item is repeated for all the fields that are required to identify the specific value in the pivot table.

Example:

Sample Pivot Table


Get Pivot Function Usage
Please refer and analyze the formula using the following example for better understating.

Sum of Sales
Sales_Period




Prod_ID
Q1-2014
Q2-2014
Q3-2014
Q4-2014
Grand Total
ABC_1234
157872
223764
163852
315189
860677
BCD_2345
207254
215188
225615
281984
930041
CDE_3456
222382
140506
156293
239015
758196
DEF_4567
224419
254021
295131
292883
1066454
EFG_5678
315015
222710
310269
272110
1120104
FGH_6789
207663
200940
193089
311924
913616
GHI_7890
221794
161097
185287
233228
801406
Grand Total
1556399
1418226
1529536
1946333
6450494


Get Pivot Function
 =GETPIVOTDATA("Sales",$B$3,"Prod_Id","DEF_4567",
"Sales_Period","Q3-2014")
Result
295131

Here in the above example shot , In the Getpivot Function , we use a cell reference '$B$3' which is refer to a Pivot_table is any cell in the pivot table, usually the upper-left corner . 

Note:
For better understanding of Getpivot Function ,  Do Practice in Real Time with Sample Data.

No comments:

Post a Comment

Follow Me by Email

ExcelKingdom-Popular Posts

ExcelKingdom-Random Posts