Written and reviewed by FormulaCraft Team. Each formula on this page is run through our verification engine before publishing.
Last reviewed:
How to use GETPIVOTDATA to pull values from a pivot table in Excel and Google Sheets
Topic:Pivot-style summaries (formula approach)Excel & Google Sheets
=GETPIVOTDATA("Sales",A1,"Region","East")Try it with your data
Edit the grid or formula, then run it through a real spreadsheet engine — no signup.
Sample data — click any cell to edit
Runs server-side · free · no signup
Step by step
- 1Build a small pivot table with your data. Place the top-left cell of the pivot at A1.
- 2Identify the value field name ('Sales') and the field/item pair you want to extract ('Region', 'East').
- 3Enter =GETPIVOTDATA("Sales",A1,"Region","East") in any cell outside the pivot.
- 4Confirm the result matches the pivot's displayed value for East region sales.
- 5Replace hardcoded item strings with cell references (e.g., D2) to make the formula dynamic.
Tips
- If you type a cell reference inside a pivot in Excel, it auto-generates a GETPIVOTDATA formula — you can copy that syntax as a starting template.
- Pass additional field/item pairs as extra argument pairs to drill into sub-totals: =GETPIVOTDATA("Sales",A1,"Region","East","Year",2024).
- To disable auto-generation of GETPIVOTDATA in Excel, go to PivotTable Analyze > Options and uncheck 'Generate GetPivotData'.
Need it for your exact data?
Describe your columns in plain English and get the precise formula for your sheet, with the right Excel or Sheets syntax.
Frequently asked
Why does GETPIVOTDATA return a #REF! error?
The field name or item label doesn't exist in the pivot. Check for trailing spaces, exact capitalisation, and that the pivot is refreshed with current data.
Can I use GETPIVOTDATA without a pivot table?
No — GETPIVOTDATA requires a pivot table as its second argument. For formula-only aggregation without any pivot, use SUMIFS or the approach on the group-by-month-without-pivot page instead.
Does it work in Google Sheets?
Yes. Google Sheets supports GETPIVOTDATA with the same syntax against a Sheets pivot table.
More on Pivot-style summaries (formula approach)
See all →Reference
GETPIVOTDATA
Extract a specific value from a PivotTable using field names and item values as explicit identifiers.
How-toHow to build a pivot-style summary with formulas
Build a formula-driven pivot-style cross-tab in Excel or Google Sheets using SUMIFS with mixed references — live totals by row and column without inserting a pivot table.
How-toHow to build a pivot-style summary with QUERY
Use Google Sheets QUERY with GROUP BY, SUM, and LABEL to build a pivot-style summary table without a manual pivot table — Excel alternative uses SUMIFS.
How-toHow to write formulas for a pivot table calculated field
Understand how pivot calculated fields work and when to replace them with SUMIFS-based formulas in Excel and Google Sheets for more control and flexibility.
How-toHow to calculate percent of total without a pivot table
Use SUMIF and SUM to calculate each category's percent of grand total in Excel and Google Sheets — no pivot table required.
How-toHow to group data by month using formulas instead of a pivot
Group and sum transaction data by month using SUMIFS with EOMONTH or TEXT in Excel and Google Sheets — no pivot table needed.