The GETPIVOTDATA function in Google Sheets is solely for those who use Pivot Table for creating summary reports and charts.
I’m not talking about Query Pivot, but the command you can access from the Insert menu.
If you use Pivot Table in Google Sheets, learning this function may come into use sometimes.
Let’s see how to use the GETPIVOTDATA function in Google Sheets, and I am starting with its purpose.
The purpose of the Getpivotdata function is clear from its name. Yes! We can use this formula to get the pivot table data.
In other words, it helps to extract the aggregated information from a Pivot Table report.
Usually, just like in any normal data range, you can get the information as shown below from a Pivot Table report. But please do note that it is not the correct way.
When the layout of the Pivot Table changes due to the changes in the source data, those changes may not reflect in the extracted value.
There comes the GETPIVOTDATA function meaningful.
GETPIVOTDATA Function: Syntax and Arguments
Let’s start with the syntax of the GETPIVOTDATA function in Google Sheets. It may be confusing for some, but I have given ample information to simplify it.
Syntax: GETPIVOTDATA(value_name, any_pivot_table_cell, [original_column, …], [pivot_item, …])
Arguments:
value_name
: The name of the value, say the field label of the aggregated column in the report for which you want to get data. You can specify it in two ways.
- If you hardcode the
value_name
(enter directly in the formula), it must be enclosed in single quotation marks. - You can also refer to a cell that contains the appropriate text.
any_pivot_table_cell
: Any cell reference in the desired pivot table. Recommend using the very first cell in your Pivot Table.
original_column
: The name of the column, or you can say field label in the source data set. You can specify it in two ways, similar to the first argument.
- If you hardcode the
original_column
(enter directly in the formula), it must be enclosed in single quotation marks. - You can also refer to a cell in the report Sheet that contains the appropriate text.
pivot_item
: The name of the row/column shown in the pivot table corresponding to the original_column
you want to retrieve.
Here also, the above two points are applicable. If it’s a date, and you specify a cell reference, use TO_TEXT around it. E.g., to_text(I2)
.
Sample Data for GETPIVOTDATA Function Experiments in Google Sheets
I have the marks of “Student 1” and “Student 2” in different subjects entered in Sheets in columns A to C.
We can follow the above sample data to learn the use of the GETPIVOTDATA function in Google Sheets.
Let’s first prepare a Pivot Table report using the above student marks.
Here are the steps to follow for that.
- Select A1:C9.
- Go to the Insert menu and select Pivot Table.
- I’m creating the report in the source sheet. So check the “Existing sheet” option.
- Enter E1 in the field immediately below the above option and select the “Create” button.
- Drag and drop “Name” and then “Subject” under “Row.”
- Then “Marks” twice under “Value”: One for Sum and the other for Average.
Here are my settings.
So you will get a Pivot Table similar to the one below. Let’s use the GETPIVOTDATA function in this report.
Examples of the GETPIVOTDATA Function in Google Sheets
The blow examples may help you understand the GETPIVOTDATA function examples clearly.
1. value_name and any_pivot_table_cell
Formula:
=GETPIVOTDATA("SUM of Marks", Sheet2422!E1)
Result: 630
In this, "SUM of Marks"
is the value_name and 'Pivot Table 1'!A1
is the any_pivot_table_cell. For your information, ‘Sheet2422’ is the tab name of the Pivot Table.
Here is one more formula in line with the above example.
Formula:
=GETPIVOTDATA("AVERAGE of Marks", Sheet2422!E1)
Result: 78.75
2. value_name, any_pivot_table_cell, original_column, and pivot_item
Formula:
=GETPIVOTDATA("SUM of Marks", Sheet2422!E1,"Name","Student 1")
Result: 312
Here is one more example.
Formula:
=GETPIVOTDATA("Average of Marks", Sheet2422!E1,"Name","Student 1")
Result: 78
I have used multiple original_column and pivot_item in the following GETPIVOTDATA function example.
Formula:
=GETPIVOTDATA("SUM of Marks", Sheet2422!E1,"Name","Student 1","Subject", "Maths")
Result: 75
You can change “Student 1” in the above GETPIVOTDATA formula with “Student 2” to extract the aggregated data of “Student 2“.
I hope you could well understand how to use the GETPIVOTDATA function in Google Sheets.
Additional Resources: