The GETPIVOTDATA function extracts the data stored in a PivotTable report. You can use this function to retrieve data as long as it is visible in the pivot table.
The easiest way to understand how the Getpivotdata function works:
To extract data from a cell in a pivot table, we can enter a normal cell link in cell D14, for example=C6.The GetPivotData function will automatically generate the formula as shown in the below screenshot:
Let’s understandarguments of this
Field & Items are entered as a pair & we can use a maximum as 126Arguments as shown in the below picture.
Using Cell References in GetPivotData
In a GetPivotData formula, refer to the pivot table, and the field(s) and item(s) that you want the data for. For example, this formula gets the Total, from the pivot table in D14, for the Month field, and the Washington item.
To make a GetPivotData formula more flexible, we can refer to worksheet cells, instead of typing the item or field names in the arguments.
Using the same example, we can enter “Apr” in cell L4 & “Washington” in cell L5. Then, change the formula in cell D14to reflect L4 & L5, instead of typing “Apr” & "Washington" in the formula.
Formula in cell D14 =GETPIVOTDATA("Sales",$B$2,"Month",L4,"Region",L5)
If you do not want to automatically generate the GetPivotData Function you can get rid of it by following the given steps:
Alternatively, Under File?Options?Click on Formulas de-select Use GetPivotData functions for PivotTable references. Click on Ok.
In this way we can extract data from pivot table.
The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.