The GETPIVOTDATA returns visible data from a pivot table.
This function is convenient for dynamically obtaining totals or specific values from a pivot table into pre-prepared reports, dashboards, or cells.
Syntax
GETPIVOTDATA(value_field, pivot_table, [field1, item1, ...])
Argument |
Description |
Permitted values |
|---|---|---|
value_field |
Name of the value field from which data is retrieved |
Text string in quotation marks |
pivot_table |
Reference to any cell, range of cells, or named range in the pivot table |
Reference to a cell |
[field1, item1, field2, item2, ...] |
(optional) Names of fields and elements within them. Allow you to narrow down your query and retrieve a specific value from the table. They are enclosed in quotation marks, except for dates and numbers. Pairs of field names and elements can follow each other in any order |
Text string |
Examples of use
Suppose that the pivot table contains sales data with the following attributes: Region, Manager, Quarter, and Sales_Amount.
We need to obtain the sales amount for manager “Ivanov” in the “North” region for the “Q3” quarter.
Solution:
=GETPIVOTDATA("Sales_Amount", $A$1, "Region", "North", "Manager", "Ivanov", "Quarter", "Q3")
where:
–"Sales_Amount": Target data field.
–$A$1: Reference to the pivot table.
– "Region", "North": First filtering condition.
–"Manager", "Ivanov": Second filtering condition.
–"Quarter", "Q3"): Third filtering condition.
The function returns a numerical value corresponding to the intersection of the specified criteria in the pivot table. If no data is found, the #REF! error is returned.
Creating a dynamic report in which query parameters can be changed via control cells
To solve this problem, we need to create a formula that retrieves data based on the values in cells B1 (data field), C1 (manager value), and D1 (quarter value).
Solution:
=GETPIVOTDATA($B$1, PivotTable_Sales, "Manager", $C$1, "Quarter", $D$1, "Status", "Completed")
where:
–$B$1: Data field from cell B1.
–PivotTable_Sales: Named range of the pivot table.
–"Manager", $C$1: Manager value from C1.
–"Quarter", $D$1: Quarter value from D1.
–"Status", "Completed": Static additional condition.
Order of operations:
1.The function searches the “PivotTable_Sales” pivot table for the value of the field specified in B1.
2.Applies filters: Manager = value from C1, Quarter = value from D1, Status = “Completed”.
3.Returns the sum, quantity, or average depending on the pivot table settings.
Example using the “[‘All’]” element
The table contains sales data with the following attributes: Region, Item, and Sales_Volume.
We need to calculate the share of sales in the “North” region out of total sales across all regions.
Solution:
=GETPIVOTDATA("Sales_Volume", $A$1, "Region", "North") / GETPIVOTDATA("Sales_Volume", $A$1, "Region", ["All"])
The first function gets sales for the “North” region, and the second one gets total sales for all regions using the [“All”] element.
The function returns a number between 0 and 1 that shows the share of the “North” region in total sales.
Notes
–The function can be created automatically by clicking on a pivot table cell while entering a formula. This option can be enabled or disabled in the application settings.
–The function is automatically updated when the source pivot table is changed or updated.
–Field and element names must exactly match the names in the pivot table.
–A special element is used to display all field elements: “[All]”.
–The function returns the #REF! error if the pivot table has been deleted or changed.