Search
Clear search
Close search
Google apps
Main menu

GETPIVOTDATA

Extracts an aggregated value from a pivot table that corresponds to the specified row and column headings.

Sample Usage

GETPIVOTDATA("SUM of number of units", 'Pivot table'!A1)

GETPIVOTDATA("AVERAGE of price per unit", A1, "division", "east")

GETPIVOTDATA("price per unit", B2, "division", "east", "subdivision", 2)

GETPIVOTDATA(A1, 'Pivot table'!A1, "division", A2)

Syntax

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

  • value_name - The name of the value in the pivot table for which you want to get data.
    • value_name must be enclosed in quotation marks or be a reference to any cell containing the appropriate text.
    • Some simple pivot tables may not show the names of values. In this case, leaving this argument blank or putting in any text will work.
  • any_pivot_table_cell - Any reference to a cell in the desired pivot table (top corner recommended).
  • original_column - [ OPTIONAL ] - the name of the column in the original data set (not the pivot table).
  • pivot_item - [ OPTIONAL ] - the name of the row or column shown in the pivot table corresponding to original_column_1 that you want to retrieve.

Notes

  • Text provided to the arguments is not case-sensitive. You can use any combination of upper or lower case letters.
  • While any cell in the pivot table may be chosen for any_pivot_table_cell, it's best to choose the cell in the top corner because if the pivot table decreases in size as data is modified and the chosen cell is no longer in the pivot table then GETPIVOTDATA will result in an error.
  • Use the DATE and TIME functions in order to properly match dates and times in the pivot table instead of dates enclosed in quotation marks.

See Also

QUERY - Runs a Google Visualization API Query Language query across data.

FILTER - Returns a filtered version of the source range, returning only rows or columns which meet the specified conditions.

DATE - Converts a provided year, month, and day into a date.

TIME - Converts a provided hour, minute, and second into a time.

Examples

Dataset for the examples below.

Pivot table using the data above.

Extracting various attributes from the pivot table using GETPIVOTDATA.

Mary is a Docs & Drive expert and author of this help page. Leave her feedback below about the page.

Was this article helpful?
How can we improve it?