HomeGoogle DocsSpreadsheetHow to Use the GETPIVOTDATA Function in Google Sheets

How to Use the GETPIVOTDATA Function in Google Sheets

Published on

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.

Get Values from Pivot Table: Wrong 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:

GETPIVOTDATA Function: Arguments Explained.

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.

Sample Student Data

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.

  1. Select A1:C9.
  2. Go to the Insert menu and select Pivot Table.
  3. I’m creating the report in the source sheet. So check the “Existing sheet” option.
  4. Enter E1 in the field immediately below the above option and select the “Create” button.
  5. Drag and drop “Name” and then “Subject” under “Row.”
  6. Then “Marks” twice under “Value”: One for Sum and the other for Average.

Here are my settings.

Pivot Table Settings (Student Data Aggregation)

So you will get a Pivot Table similar to the one below. Let’s use the GETPIVOTDATA function in this report.

Examples of GETPIVOTDATA Function in Google Sheets.

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:

  1. How to Get a Single Value from a Pivot Table in Google Sheets.
  2. Extract Total and Grand Total Rows From a Pivot Table in Google Sheets.
Prashanth KV
Prashanth KVhttps://infoinspired.com/about-me/
Introducing 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.

Creating a Running Balance with Dynamic Array Formulas in Excel

For calculating a running balance, we can utilize the SCAN function, which is a...

Comparing the FILTER Function in Excel and Google Sheets

I believe comparing the differences in how the FILTER functions work in Excel and...

Quartile IF in Google Sheets

To calculate quartiles based on conditions, referred to as "Quartile IF," we will use...

Drop Rows and Columns by Index Numbers in Google Sheets

With the help of two simple custom formulas, we can drop rows and columns...

More like this

Quartile IF in Google Sheets

To calculate quartiles based on conditions, referred to as "Quartile IF," we will use...

Drop Rows and Columns by Index Numbers in Google Sheets

With the help of two simple custom formulas, we can drop rows and columns...

Reference a Column by Field Label in Google Sheets QUERY

Here we'll explore the two best methods for referencing a column by its field...

LEAVE A REPLY

Please enter your comment!
Please enter your name here