Lookup & Reference
All versions
◉ 31 views
=GETPIVOTDATA()
The GETPIVOTDATA function returns visible data from a PivotTable.
Applies To:
Excel for Microsoft 365Excel for Microsoft 365 for MacExcel 2024Excel 2024 for MacExcel 2021Excel 2021 for MacExcel 2019Excel 2016
=GETPIVOTDATA(data_field, pivot_table, [field1, item1, field2, item2], ...)
Hover argument names in the table for details.
Output typeMixed
Popularity45 / 100
VolatileNo
Dynamic ArrayNo
Download sample .xlsx ▦ Try in Playground
Dedicated file for this function — safe & lightweight · sample data + live formulas
⬇ Download Sample Workbook
A dedicated .xlsx for =GETPIVOTDATA() with Persian sample data & live formulas — open in Excel and practice.
- Sheet “Sample” — data table + formula in place
- Sheet “Guide” — syntax, arguments & tips from this page
- Instant & secure (token + rate limit)
Quiz this function
%d questions — syntax, example & errors — test yourself!
◧ Arguments
| Argument | Type | Required | Description |
|---|---|---|---|
data_field |
Text | Required | 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. |
pivot_table |
Reference | Required | 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. |
field1, item1, field2, item2... |
Date | Optional | 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]" |
▣ Examples
$5,534
=GETPIVOTDATA("Sales", $A$3)
Result:
Returns the grand total of the Sales field.
$5,534
=GETPIVOTDATA("Sum of Sales", $A$3)
Result:
Also returns the grand total of the Sales field. The field name can be entered exactly as it looks on the sheet, or as its root (without "Sum of", "Count of", and so on).
$2,876
=GETPIVOTDATA("Sales", $A$3, "Month", "Mar")
Result:
Returns the total sales for March.
$309
=GETPIVOTDATA("Sales", $A$3, "Month", "Mar", "Product", "Produce", "Sales Person", "Buchanan")
Result:
Returns the total produce sales in March for Buchanan.
#REF!
=GETPIVOTDATA("Sales", $A$3, "Region", "South")
Result:
Returns a #REF! error because the South region data isn't visible due to the filter.
#REF!
=GETPIVOTDATA("Sales", $A$3, "Product", "Beverages", "Sales Person", "Davolio")
Result:
Returns a #REF! error because there is no total beverage sales data for Davolio.
💬 Comments & Q&A 0 approved
🔒 Your comment will be published after review.Share your thoughts — visible after admin approval
No comments yet — be the first!