GETPIVOTDATA Function in Microsoft Excel: Syntax, Examples & Troubleshooting
Retrieve Excel PivotTable values with GETPIVOTDATA. Build a local reproducible PivotTable, select field/item pairs, use stable anchors and diagnose visible-data #REF! errors.
Verification status: See the evidence and test scope described in this guide. How formula examples are checked
Quick answer: GETPIVOTDATA retrieves visible data from an existing PivotTable by field and item names. It does not create a PivotTable and does not summarize an arbitrary raw range on its own. Use an anchor cell inside the intended PivotTable.
Syntax and argument rules
=GETPIVOTDATA(data_field, pivot_table, [field1, item1, field2, item2], ...)
data_field and pivot_table are required. Data field is the quoted name of the value field, such as “Sales” or its displayed “Sum of Sales” caption. Pivot_table is a reference to a cell/range inside the PivotTable.
Up to 126 field/item pairs can specify a slice. Field names and nonnumeric, nondate item names are quoted. Date items should use a date serial or DATE expression; time items can use a decimal or TIME expression. Arguments may be cell references, enabling a small selector panel.
The function returns visible PivotTable information, including applicable calculated items or custom calculations. If one referenced range spans several PivotTables, Microsoft says the most recently created one is used; a single clear anchor avoids that ambiguity.
Worked example: create a local PivotTable fixture
Use this fixed source table in A1:D7. Sales values are numeric.
| Row | A · Region | B · Quarter | C · Sales | D · Status |
|---|---|---|---|---|
| 1 | Region | Quarter | Sales | Status |
| 2 | East | Q1 | 100 | Paid |
| 3 | East | Q2 | 150 | Paid |
| 4 | West | Q1 | 80 | Paid |
| 5 | West | Q2 | 120 | Pending |
| 6 | East | Q1 | 50 | Pending |
| 7 | West | Q2 | 30 | Paid |
- Select A1:D7 and use Insert → PivotTable to create a PivotTable on the same worksheet beginning at F2.
- Put Region in Rows, Quarter in Columns and Sales in Values.
- Set Sales to Sum, not Count. Leave both regions and quarters visible and enable the ordinary grand totals.
- Outside the PivotTable, enter
=GETPIVOTDATA("Sales",$F$2).
Expected result: 530, the sum of all six source amounts. These instructions describe how to build the fixture in Excel; no PivotTable was executed or screenshotted for this guide.
Second example: request one region-quarter combination
=GETPIVOTDATA("Sales",$F$2,"Region","East","Quarter","Q1")
Expected result: 150, from source values 100 and 50. Removing the Quarter/Q1 pair gives East’s total 300. Asking for West with Quarter/Q2 gives 150, from 120 and 30.
The formula follows the field/item meaning instead of relying on the cross-tab cell staying at a particular row and column. However, renaming fields or removing the requested item can still change or invalidate that meaning.
Advanced application: build selectors and audit the result
Enter East in K2 and Q1 in L2. Use =GETPIVOTDATA("Sales",$F$2,"Region",K2,"Quarter",L2) in M2. Expected result is 150. Changing K2 to West while leaving Q1 selects 80.
A raw-data control for the first selector state is =SUMIFS(C2:C7,A2:A7,K2,B2:B7,L2), also 150 while the PivotTable is current and displays the same data. If the source is edited, refresh the PivotTable before assuming it reflects the edit. A stale PivotTable and a live SUMIFS can legitimately disagree.
You can ask Excel to generate a suitable formula by typing = in an output cell and clicking a visible PivotTable result. The Generate GetPivotData setting controls this behavior; it is a formula-entry convenience, not a prerequisite for manually typing the function.
Troubleshooting and visibility limits
Microsoft documents #REF! when the anchor does not identify a PivotTable or when field/item arguments do not describe visible data, including data excluded by a report filter. Filtering out West can therefore make a West request fail rather than return an invented zero.
If a field/item combination selects a single cell, Microsoft says its value can be text, number, error or blank; GETPIVOTDATA is not inherently a numeric-only function. Do not blanket-wrap every #REF! as zero before checking whether the selection is valid.
OLAP PivotTables use dimension and item names with additional source-specific syntax. This guide’s fixture is an ordinary local PivotTable and makes no claim about a cube connection, authenticated backend or unavailable data model. A formula-based summary that merely looks like a PivotTable is not an equivalent anchor.
Compatibility and verification
Microsoft’s reference checked on October 17, 2026 lists: Excel for Microsoft 365 Excel for Microsoft 365 for Mac Excel 2024 Excel 2024 for Mac Excel 2021 Excel 2021 for Mac Excel 2019 Excel 2016. This current support list does not establish the first release. The introduction version and last functioning version have not been established from the reviewed documentation. No retirement is asserted.
Example verification: Expected numerical results are independently calculated; text and shape expectations follow the documented contract. These examples have not been tested in Microsoft Excel. No screenshot or native execution is claimed.
Related guides and source
SUMIFS in Excel | SUM in Excel | DATE in Excel | TIME in Excel
- Microsoft GETPIVOTDATA reference, retrieved October 17, 2026.