The GETPIVOTDATA function in Google Sheets is solely for those who make the use of Pivot Table report. So If you use Pivot Table in Google Sheets, learning this function may come in use at some times. Let’s see how to use Getpivotdata function in Google Sheets.
Purpose of Google Sheets GETPIVOTDATA Formula
The purpose of Getpivotdata function is clear itself from the function name. We can use this formula to get pivot table data or to extract required information from a Pivot Table.
Normally, just like in any normal data range, you can get required information as above. But the problem here is, when the layout of the Pivot Table changes, due to the changes in source data, that changes may not reflect in the extracted value. That’s why we require a function like Getpivotdata.
How to Use GETPIVOTDATA Function in Google Sheets
See the syntax.
Syntax: GETPIVOTDATA(value_name, any_pivot_table_cell, [original_column, pivot_item, …])
I’m not going to explain any of the syntax elements or arguments at this point of time. That you can easily grasp from our examples below.
To learn the use of Getpivotdata function, we can follow our below sample data. I’ve the marks of two students in different subjects for this purpose.
From this sample data, I am going to prepare a Pivot Table Report.
You can enter the above sample data on a blank spreadsheet. Once completed, select your entered data, then go to the Data menu and click Pivot Table. See the settings below.
Pivot Table Report Settings
Range and Rows Settings:
Values Field Formula Settings:
Pivot Table Report
When you apply the above settings on your sheet, you would get a Pivot Table report as below. On this report, we are going to apply different GETPIVOTDATA formulas.
How to Use GETPIVOTDATA Formula in Google Sheets
1. Find SUM of marks of all students.
Formula: =GETPIVOTDATA(“SUM of Marks”, ‘Pivot Table 1’!A1)
Syntax: =GETPIVOTDATA(value_name, any_pivot_table_cell)
Here “SUM of Marks” is the value_name and ‘Pivot Table 1’!A1 is the any_pivot_table_cell. I suggest you to always stick with cell A1. For your information, Pivot Table 1 is the sheet name of Pivot Table.
2. Find Average of marks of all students.
Formula: =GETPIVOTDATA(“AVERAGE of Marks”, ‘Pivot Table 1’!A1)
3. Find SUM of marks of Student 1.
Formula:=GETPIVOTDATA(“SUM of Marks”, ‘Pivot Table 1’!A1,”Name”,”Student 1″)
Syntax: GETPIVOTDATA(value_name, any_pivot_table_cell, original_column, pivot_item)
4. Find Average of marks of Student 1.
Formula: =GETPIVOTDATA(“Average of Marks”, ‘Pivot Table 1’!A1,”Name”,”Student 1″)
5. Find Sum of marks of Student 1 in Subject Maths.
Formula: =GETPIVOTDATA(“SUM of Marks”, ‘Pivot Table 1’!A1,”Name”,”Student 1″,”Subject”, “Maths”)
You can change Student 1 in the above formulas with Student 2 to extract the data that pertaining to Student 2.
I believe you could follow our above tutorial How to Use GETPIVOTDATA Function in Google Sheets. I am not a user of GETPIVOTDATA because I depend the QUERY function to generate Pivot Report.