Check Sheet Logo
Check Sheet

GETPIVOTDATA

The GETPIVOTDATA formula retrieves data stored in a pivot table. It takes as input the name of the value to retrieve, a reference to any cell within the pivot table, and optionally the names of one or more columns and/or pivot items that filter the data to retrieve. This formula is commonly used when you want to retrieve specific data from a pivot table and use it in other calculations.

Usage

Use the GETPIVOTDATA formula with the syntax shown below, it has 2 required parameters and 2 optional parameters:

=GETPIVOTDATA(value_name, any_pivot_table_cell, [original_column, ...], [pivot_item, ...]
Parameters:
  1. value_name (required):
    The name of the value to retrieve from the pivot table.
  2. any_pivot_table_cell (required):
    A reference to any cell within the pivot table, used to specify which pivot table to retrieve data from.
  3. original_column, ... (optional):
    Optional parameter(s) that specify the name(s) of the column(s) in the source data that the pivot table summarizes. If more than one column is specified, this parameter must be enclosed in square brackets.
  4. pivot_item, ... (optional):
    Optional parameter(s) that specify the name(s) of the pivot item(s) that filter the data to retrieve. If more than one pivot item is specified, this parameter must be enclosed in square brackets.

Examples

Here are a few example use cases that explain how to use the GETPIVOTDATA formula in Google Sheets.

Retrieve a total from a pivot table

Suppose you have a pivot table that summarizes sales data by region and month, and you want to retrieve the total sales for the 'West' region for the month of 'January'. You can use the GETPIVOTDATA formula to retrieve this value by entering the formula =GETPIVOTDATA("Sales", A1, "Region", "West", "Month", "January") in a separate cell. This will return the total sales for the specified region and month.

Calculate a percentage of a pivot table value

Suppose you have a pivot table that summarizes sales data by region and product, and you want to calculate the percentage of total sales that come from a specific product in a specific region. You can use the GETPIVOTDATA formula to retrieve the total sales for the specified region and product, and then divide that value by the total sales for the entire region. For example, if cell A1 contains the pivot table, you could enter the formula =GETPIVOTDATA("Sales", A1, "Region", "West", "Product", "Product X")/GETPIVOTDATA("Sales", A1, "Region", "West") to calculate the percentage of total sales that come from 'Product X' in the 'West' region.

Retrieve data from a pivot table using cell references

You can also use GETPIVOTDATA to retrieve data from a pivot table using cell references instead of hard-coding column and pivot item names. For example, you could use the formula =GETPIVOTDATA(B1, A1, C1:D2) to retrieve the value in the pivot table at the intersection of the row and column specified by cells C1 and D1, respectively.

Common Mistakes

GETPIVOTDATA not working? Here are some common mistakes people make when using the GETPIVOTDATA Google Sheets Formula:

Incorrectly referencing a non-existent pivot table cell

One common mistake is referencing a non-existent pivot table cell. This can lead to the formula returning an error. To fix this, ensure that the cell reference is correct and that the pivot table exists.

Using incorrect syntax

Another common mistake is using incorrect syntax. This can occur when the user does not include all required arguments or includes extra arguments that are not needed. To fix this, check the syntax and ensure that all required arguments are included.

Using incorrect column or item names

It is important to use the correct column and item names when using the GETPIVOTDATA formula. Using incorrect names can result in the formula returning an error or incorrect data. To fix this, ensure that the names are correct and match the names in the pivot table.

Not including the value name argument

One common mistake is forgetting to include the value name argument. This is required for the formula to work properly. To fix this, include the value name argument in the formula.

Not using absolute cell references for pivot table cells

When referencing pivot table cells in the GETPIVOTDATA formula, it is important to use absolute cell references. Otherwise, the formula may return incorrect data if the pivot table is moved or resized. To fix this, use absolute cell references for the pivot table cells.

Related Formulas

The following functions are similar to GETPIVOTDATA or are often used with it in a formula:

  • QUERY

    The QUERY function in Google Sheets is used to query data from a specified dataset. The function allows users to filter and sort data based on specific criteria. The function is commonly used in data analysis and reporting, as well as in creating dynamic dashboards.

  • SUMIF

    The SUMIF formula is used to add up values in a range that meet a specific criterion. It can be used to sum values based on text, numbers, or dates. The formula is most commonly used in financial analysis, budgeting, and data analysis.

  • SUMIFS

    The SUMIFS formula is a function in Google Sheets that adds the values in a specified range based on multiple criteria. It is most commonly used to sum data that meets specific criteria, such as summing sales for a particular month by region or summing expenses for a specific category and date range.

  • VLOOKUP

    The VLOOKUP function is a lookup formula used to search for a value in the first column of a range of cells (the search key) and return a value in the same row from a specified column in that range. This function is most commonly used to look up and retrieve data from a table.

Learn More

You can learn more about the GETPIVOTDATA Google Sheets function on Google Support.