GETPIVOTDATA function
Returns data stored in a PivotTable report
Syntax
GETPIVOTDATA(dataField, pivotTable, [field1], [item1])Square brackets indicate optional arguments.
Arguments
| Argument | Required | Meaning |
|---|---|---|
| dataField | Yes | The name of the PivotTable field that contains the data that you want to retrieve. This needs to be in quotes. Example: =GETPIVOTDATA("Sales", A3). Here, "Sales" is the Values field that we want to retrieve. Since no other field is specified, GETPIVOTDATA returns the total sales amount."Sales" |
| pivotTable | Yes | A reference to any cell, range of cells, or named range of cells in a PivotTable. This information is used to determine which PivotTable contains the data that you want to retrieve. Example: =GETPIVOTDATA("Sales", A3). Here, A3 is a reference inside the PivotTable and tells the formula which PivotTable to use.A3 |
| field1 | No · repeats | 1 to 126 pairs of field names and item names that describe the data that you want to retrieve. The pairs can be in any order. Field names and names for items other than dates and numbers need to be enclosed in quotation marks. Example: =GETPIVOTDATA("Sales", A3, "Month", "Mar"). Here, "Month" is the field and "Mar" is the item. To specify multiple items for a field, enclose them in curly braces (for example: {"Mar", "Apr"}). For OLAP PivotTables , items can contain the source name of the dimension and also the source name of the item. A field and item pair for an OLAP PivotTable might look like this: "[Product]","[Product].[All Products].[Foods].[Baked Goods]""Month" |
| item1 | No · repeats | 1 to 126 pairs of field names and item names that describe the data that you want to retrieve. The pairs can be in any order. Field names and names for items other than dates and numbers need to be enclosed in quotation marks. Example: =GETPIVOTDATA("Sales", A3, "Month", "Mar"). Here, "Month" is the field and "Mar" is the item. To specify multiple items for a field, enclose them in curly braces (for example: {"Mar", "Apr"}). For OLAP PivotTables , items can contain the source name of the dimension and also the source name of the item. A field and item pair for an OLAP PivotTable might look like this: "[Product]","[Product].[All Products].[Foods].[Baked Goods]""March" |
Practice with a PivotTable
This function needs a PivotTable context. Choose a report, calculate an editable query and download its formula with the native PivotTable.
Open PivotTable query practice →Reference and compatibility
Univer 1.0.4 / Apache-2.0. Calculation checks cover the supplied examples only. The browser worksheet does not implement every Excel feature, external service or connected data type.