Using the GETPIVOTDATA Function in Google Sheets: A Guide

Published on

The GETPIVOTDATA function in Google Sheets is specifically designed for those who use Pivot Tables to create summary reports and charts.

If you frequently use Pivot Tables in Google Sheets, learning how to use this function can be quite beneficial.

The purpose of the GETPIVOTDATA function is clear from its name—it allows you to retrieve data from a Pivot Table. In other words, it helps extract aggregated information from a Pivot Table report.

For example, if your Pivot Table range is A1:D12, you could directly reference the aggregated value in C11 using =C11. However, this is not the ideal approach. Do you know why?

When the layout of the Pivot Table changes due to modifications in the source data, those changes might not be reflected in the directly referenced value. This is where the GETPIVOTDATA function becomes invaluable.

You can use the formula like this:

=GETPIVOTDATA("SUM of Marks", A1, "Name", "Student 2")

This formula extracts the “SUM of Marks” (aggregated value) for “Student 2” from the Pivot Table starting at cell A1. The data is grouped by the “Name” column, with “Student 2” being one of the names in that column.

GETPIVOTDATA Function: Syntax and Arguments (With Illustration)

Let’s begin with the syntax of the function. It may seem confusing to some users, but I’ve provided clear explanations to simplify it.

Syntax:

GETPIVOTDATA(value_name, any_pivot_table_cell, [original_column, …], [pivot_item, …])
GETPIVOTDATA Function: Syntax and Arguments Explained with Illustration

Arguments:

  • value_name: The name of the value, or the field label of the aggregated column in the Pivot Table, for which you want to retrieve data.
  • any_pivot_table_cell: A reference to any cell within the desired Pivot Table. It’s recommended to use the very first cell in your Pivot Table so that any shrinking or expansion of the table does not affect the reference.
  • original_column: The name of the column (field label) in the source data set used for grouping.
  • pivot_item: The item in the original_column within the Pivot Table for which you want to retrieve the aggregated value.

In layman’s terms, when using the GETPIVOTDATA function, first specify the label of the aggregated column in the Pivot Table, then reference the first cell in the Pivot Table. Next, specify the grouped column label and item from the source data, followed by the second grouped column label and item, if applicable.

value_name, original_column, and pivot_item can be specified as a cell reference or hardcoded (entered directly) in the formula. Be mindful when pivot_item is a date—it should be formatted exactly as it appears within the Pivot Table, for example, TEXT(DATE(2024, 10, 30), "DD/MM/YYYY") or TEXT(A100, "DD/MM/YYYY").

Creating a Sample Pivot Table

Enter or copy-paste the following data into cells A1:C9 of a new sheet.

NameSubjectMarks
Student 1English89
Student 1Maths75
Student 1Physics92
Student 1Chemistry56
Student 2English94
Student 2Maths78
Student 2Physics86
Student 2Chemistry60

To create a Pivot Table for learning the GETPIVOTDATA function, follow these steps:

  1. Select cells A1:C9.
  2. Click Insert > Pivot Table.
  3. Choose Existing Sheet.
  4. Enter E1 in the field immediately below the above option.
  5. Click Create.

In the Pivot Table editor panel on the sidebar:

  1. Drag and drop Name and Subject under Rows.
  2. Drag and drop Marks twice under Values—one for Sum and the other for Average.

You will get a Pivot Table similar to the one shown below. Let’s use the GETPIVOTDATA function with this report.

Sample Pivot Table Used for Formula Examples

Code Your First GETPIVOTDATA Formula: Step-by-Step

Parameter 1: value_name

In this Pivot Table, there are two value names: “SUM of Marks” and “AVERAGE of Marks.”

Let’s use “SUM of Marks” to retrieve a value from this Pivot Table column. The formula starts as:

=GETPIVOTDATA("SUM of Marks",

Parameter 2: any_pivot_table_cell

Specify the first cell of the Pivot Table, which is E1:

=GETPIVOTDATA("SUM of Marks", E1)

You can close the bracket here since the other arguments are optional. The formula will return the total marks of students, which is 630.

GETPIVOTDATA Function Example 1

Parameter 3: original_column

This should be the first column used for grouping, which contains the student names. The field label of this column in the source data is “Name”:

=GETPIVOTDATA("SUM of Marks", E1, "Name",

Parameter 4: pivot_item

There are two items under “Name”: “Student 1” and “Student 2.” To get the total marks for “Student 1,” specify it:

=GETPIVOTDATA("SUM of Marks", E1, "Name", "Student 1")

This will return the total marks for Student 1, which is 312.

GETPIVOTDATA Function Example 2

We can add one more level by specifying the second column in the grouping and the corresponding value. Here’s how:

=GETPIVOTDATA("SUM of Marks", E1, "Name", "Student 1", "Subject", "Physics")

This will return the marks for Student 1 in Physics.

GETPIVOTDATA Function Example 3

This is how you can use the GETPIVOTDATA function in Google Sheets.

GETPIVOTDATA Function and Row and Column Grouping

We have grouped the data by names and subjects using row grouping in the Pivot Table. Therefore, the original_column argument in all the GETPIVOTDATA function examples above comes from these row groups.

If you create a Pivot Table from the sample data with “Names” under “Rows” and “Subject” under “Columns,” you might find it confusing to use the GETPIVOTDATA function.

However, the formulas will not change. You should use the first original_column from the “Rows” grouping, which is the “Name” column, and the second original_column from the “Columns” grouping, which is the “Subject” column.

So, the formulas will remain the same as described above.

Resources

  1. Extract Total and Grand Total Rows From a Pivot Table in Google Sheets
  2. Unlock the Power of GETPIVOTDATA Arrays in Google Sheets
  3. Using GETPIVOTDATA with Grouped Dates in Google Sheets
Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Hierarchical Numbering Sequences in Excel

Creating hierarchical numbering sequences in an Excel spreadsheet can significantly improve the way you...

How to Easily Repeat a Sequence of Numbers in Excel

Excel offers multiple ways to accomplish tasks, and the simplicity of each approach depends...

Create a Sequence of Dates at Every Nth Row in Excel (Dynamic Array)

Would you like to create a sequence of dates in every nth row in...

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

More like this

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

Limit Formula Expansion to a Specific Row in Google Sheets

In this tutorial, I’ll explain how to limit the expansion of an array formula...

3-D Referencing Structured Data Tables in Google Sheets

When you have several tables within a single sheet—not across multiple sheets in a...

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.