PivotdrillGETPIVOTDATA
GETPIVOTDATA("Revenue", $A$3, "Region", "East") returns the Sum of Revenue for East from the pivot that starts at A3, and keeps working when the pivot is resorted or refreshed. Excel writes it automatically when you click a pivot cell in a formula; turn that off under PivotTable Analyze, Options, Generate GetPivotData.
Syntax
=GETPIVOTDATA(data_field, pivot_table, [field1, item1], [field2, item2], ...)| Part | What it is |
|---|---|
data_field | Name of the value field, in quotes: "Revenue" or "Sum of Revenue" |
pivot_table | Any cell inside the pivot, usually the top left corner |
field1, item1 | Pairs of a field name and the item to look up; omit all pairs for the Grand Total |
Examples
=GETPIVOTDATA("Revenue",$A$3,"Region","East")Result: 12000 (East's total revenue in the pivot at A3).
=GETPIVOTDATA("Revenue",$A$3)Result: 40000 (the Grand Total, since no field and item pairs are given).
=GETPIVOTDATA("Revenue",$A$3,"Region",E1,"Product",F1)Result: The revenue for the region in E1 and product in F1, so the formula can be filled down a list.
=GETPIVOTDATA("Revenue",$A$3,"Order Date",DATE(2025,3,1))Result: March 2025 revenue when the date field is grouped by months; pass a real date, not the text Mar.
Common mistakes
- #REF! error: the item is not visible in the pivot (filtered out or collapsed), or a name is misspelled.
- The formula hard-codes "East" when you wanted it to copy down: replace the item text with a cell reference.
- You get GETPIVOTDATA when you only wanted =B5: click PivotTable Analyze, Options dropdown, untick Generate GetPivotData, or type the reference by hand.
- Grouped months return #REF!: use a date inside that month, such as DATE(2025,3,1), as the item.
Practice GETPIVOTDATA
These drills check your answer on live data, free, no account needed.
- Revenue by regionBuild a pivot that shows total Revenue for each Region.
- Region by product gridShow total Revenue with Region down the side and Product across the top.
Questions
How do I turn off GETPIVOTDATA?
PivotTable Analyze, click the arrow next to Options, untick Generate GetPivotData. Or File, Options, Formulas, untick Use GetPivotData functions for PivotTable references.
Does GETPIVOTDATA work in Google Sheets?
Yes, with the same shape: =GETPIVOTDATA("SUM of Revenue", A1, "Region", "East"). The value name must match the pivot header, including SUM of.
Related
- Refresh a pivot tableRefresh a pivot table after the source changes with right-click Refresh or Alt+F5, refresh every pivot with Ctrl+Alt+F5, and set the file to refresh on open.
- Copy a pivot table as valuesTurn a pivot table into plain cells with Copy, Paste Special, Values and Number Formats, keep the look with Keep Source Formatting, set tabular layout first.
- Pivot table in Google SheetsCreate a pivot table in Google Sheets with Insert, Pivot table, use the editor to add Rows, Columns, Values and Filters, change SUM to COUNTA, group dates.