How to Use GETPIVOTDATA Function in Google Sheets

How to Use GETPIVOTDATA Function in Google Sheets
How to Use GETPIVOTDATA Function in Google Sheets

The GETPIVOTDATA function in Google Sheets helps you extract the pivot table data from Google Sheets’ Pivot Table report.

The return value of the GETPIVOTDATA function is an aggregated result from a pivot table that matches the supplied row and column headers.

The rules for GETPIVOTDATA function in Google Sheets are as follows:

  • Field names are case sensitives. Make sure to input the exact field name in the formula. 
  • GETPIVOTDATA will not work with the column name if your Pivot Table source data has a custom heading. Thus, when you input the field name, input the custom heading instead of the column name. 
  • When putting the range, always start with the topmost cell, A1. This is to lessen the technical error that may arise if the pivot table’s range size decreases. 
  • The Pivot Table must show the value of the data that you need to extract. Otherwise, the return value of the GETPIVOTDATA function will result in an error.

Let’s make a sample scenario. 

Mark is working as an auditor in one of the busiest innovative malls in the City. Mark’s boss, Kyle, wants him to extract the purchases of his girlfriends, Jan and Ashley, and consolidate it. He wants to make sure that what was on his list corresponds to the Pivot Table.

Mark uses the GETPIVOTDATA function to filter and extract the data from the Pivot Table easily. In just a second, Mark pulls up the following results:

Wondering how he did it? I’ll teach you how, but first let’s learn the basics.

How to Use GETPIVOTDATA Function in Google Sheets

 

The Anatomy of the GETPIVOTDATA Function

So the syntax (the way we write) the GETPIVOTDATA function is as follows:

=GETPIVOTDATA(VALUE_NAME,ANY_PIVOT_TABLE_CELL, [ORIGINAL_COLUMN, PIVOT_ITEM, ...])
  • = every function in the Google Sheet always starts with an equal sign.
  • GETPIVOTDATA() This function is used to extract the aggregated data from a Google Sheet Pivot Table report.
  • VALUE_NAME is the name of the reference cell where you want to extract the data.
  • ANY_PIVOT_TABLE_CELL refers to any reference cell in the pivot table. For this argument, it is advisable to use the topmost right cell of the pivot table.
  • ORIGINAL_COLUMN This is an optional argument that refers to a source data set column. The column is not part of the pivot table report.
  • PIVOT_ITEM refers to the name of the pivot table’s row or column that corresponds to the ORIGINAL_COLUMN that you want to extract. 

 

A Real Example of Using GETPIVOTDATA Function

Here’s an example of how the GETPIVOTDATA function works in Google Sheets.

But first, you need to have a Pivot Table report as a source. If you have none, let us create one in the Google Sheet spreadsheet.

Here is the data that we will use to create the Pivot Table report.

How to Use GETPIVOTDATA Function in Google Sheets

To make a Pivot Table, we need to go to the Insert menu and click on Pivot TableHowever, in some instances, Google Sheet will not show the Pivot Table under the Insert menu. 

What you can do is click on the ‘Help‘ menu on the toolbar. After clicking the Help menu, a search bar will pop up. Type in ‘Pivot Table in the search bar.

Here’s what it looks like:

How to Use GETPIVOTDATA Function in Google Sheets

Once the Pivot Table menu is up, select the range you want to add to the Pivot Table.

function

Click on Insert, then click on Pivot Table.

insert pivot table

The next step will look like this:

How to Use GETPIVOTDATA Function in Google Sheets

The Data Range is the range of the cells that you want to convert to a pivot table.

If you want to create a different sheet for your Pivot Table, you can click on the new sheet. Otherwise, select the existing sheet. 

Here’s how it looks after applying the Pivot Table function. You need to click ‘Add‘ to add the selected values from your source sheet. 

How to Use GETPIVOTDATA Function in Google Sheets

For a detailed how-to guide on creating a Pivot Table, you may check this article How to Create a Pivot Table Report to Summarize Data in Google Sheets.

Here’s the final out of our Pivot Table. 

How to Use GETPIVOTDATA Function in Google Sheets

Let us now put it into practice. Before we proceed, you may click the link below to use the sample spreadsheet as you follow the guide.

Now we will extract the grand total from each buyer and the total sales from this Pivot Table to our source sheet, Sheet1 Sample.

To extract the data from the Pivot Table, we will use this formula:

=GETPIVOTDATA(value_name, any_pivot_table_cell, [original_column, ...], [pivot_item, ...])

 

How to Use GETPIVOTDATA Function in Google Sheets

  1. First, substitute the source formula with the given data using this syntax:

How to Use GETPIVOTDATA Function in Google Sheets

The formula will now look like any of the formulas below.

 Grand Total (Sales): 

=GETPIVOTDATA(“SUM of Prices”, ‘Pivot Table 1 Sample’!A1:D20)

 Grand Total Sales (from Buyer Jan): 

=GETPIVOTDATA(“SUM of Prices”, ‘Pivot Table 1 Sample’!A1,”Buyer”,”Jan”)

 2. In the source sheet, input the formula on the specified cell. Always start your formula with an ‘=‘ sign before the function.

In the source sheet, input the formula on the specified cell. Always start your formula with an '=' sign before the function.

3. After putting the function, add the field name of the cell where you want to extract the data from. Always add a comma after the field name.

In the source sheet, input the formula on the specified cell. Always start your formula with an '=' sign before the function.

Note: The field name should be enclosed with a ‘‘ sign.

4. We will now add the Sheet name and the range cell of the Pivot Table where we will extract the data from.

In the source sheet, input the formula on the specified cell. Always start your formula with an '=' sign before the function.

5.  Add the specific cell name and the specific value name or data that you want to extract.

Here, we will be extracting the total sales from the Buyer named Jan. 

How to Use GETPIVOTDATA Function in Google Sheets

6. Here’s Buyer Jan’s Grand Total:

How to Use GETPIVOTDATA Function in Google Sheets

7. Cross-check the data with the Pivot Table’s data.

How to Use GETPIVOTDATA Function in Google Sheets

That’s it! It’s just that simple. Now, for your practice activity, find the return value of Buyer Ashley’s Grand Total. 

 

Frequently Asked Questions

Why do I get an error on GETPIVOTDATA Function in Google Sheets?

There are various reasons why you get error messages when performing the GETPIVOTDATA function. But the most common causes why you are getting an #ERROR! error message are as follows: 

  • The syntax – If the formula is not written correctly, it will yield an #ERROR! or #REF! error message.
  • The Pivot Table – If the Pivot table’s range decreases, it will display the #ERROR! error message. This is why you should start the range with the topmost cell to cover the whole sheet. So when the Pivot Table’s size decreases, the GETPIVOTDATA function will still work.

That’s it for today’s Google Sheet how-to guide. Start using them now! Don’t forget to sign up for our newsletter for more Google Sheet spreadsheet how-to articles like this.

Get emails from us about Google Sheets.

Our goal this year is to create lots of rich, bite-sized tutorials for Google Sheets users like you. If you liked this one, you'll love what we are working on! Readers receive ✨ early access ✨ to new content.

0 Shares:
Leave a Reply

Your email address will not be published. Required fields are marked *

You May Also Like