GETPIVOTDATA function in Excel

Hello all. Another short section regarding getpivotdata function in excel. Getpivotdata function uses / queries the existing Pivot table and returns data based on specified conditions as arguments.


=getpivotdata(data_field, pivot_table, [field1,item1],..)

Here, data_field is the name of the value field to be queried. Pivot_table is the reference to any cell in the pivot table. Field1&Item1 has to be provided as pair which is the condition for the data that has to be queried.

Working Example

In the below example, we have sales data for different cars and created a simple pivot table. Let’s query the pivot table to identify North (K3) region sales for Ciaz (I6)

The above example retrieves from the field “Test” which is I2, then looks for Region as K3 (North) and looks for Product as I6 (Ciaz). The result is 315255.

Let’s use the same dataset and use the formula, but this time without any optional argument.

Here, the datafield can be any value within the pivot table. pivot_table has to be either the row header (car names) or the region header(north,south,west,east). The function would return the total of the pivot_table value.

I hope you like this short tutorial on getpivotdata function. Thanks for reading!

Leave a Reply

%d bloggers like this: