Pivot Table Issues

Fix GETPIVOTDATA REF Error When PivotTable Fields Are Collapsed

Guest Writer Sep 30, 2026 869 views

Question details

The user needs the GETPIVOTDATA function to successfully retrieve values from a PivotTable even when specific fields (like year or month) are collapsed.

How to Fix GETPIVOTDATA #REF! Error with Collapsed PivotTable Fields

Product: Excel
Device & OS: not provided
Scenario: Attempting to create summary reports using GETPIVOTDATA while keeping the source PivotTable fields collapsed for a cleaner visual layout.
Observed behavior: The GETPIVOTDATA function returns a #REF! error when the referenced item is hidden due to collapsed PivotTable fields.

Before you start

Verify that the requested field item name is spelled correctly in your formula and that the underlying PivotTable has been recently refreshed to include the latest data.

Solution 1Recommended

Expand the Collapsed PivotTable Fields

Because GETPIVOTDATA requires referenced items to be visible in the PivotTable layout, expanding the collapsed fields is the standard and most direct way to resolve the #REF! error.

Excel is intentionally designed so that GETPIVOTDATA only retrieves data currently present and visible in the PivotTable grid. When you collapse a field, that data node is removed from the visible layout, rendering it unavailable to the function.

Locate the collapsed field

Navigate to your PivotTable and find the specific field (e.g., Year or Month) that your GETPIVOTDATA formula is trying to reference.

Expand the field

Click the plus (+) icon next to the collapsed field name, or right-click the field, select 'Expand/Collapse', and choose 'Expand'.

Verify the formula

Check your GETPIVOTDATA formula cell. It should now successfully display the calculated value instead of the #REF! error.

Expected Behavior: There is currently no built-in Excel setting to force GETPIVOTDATA to read collapsed items. If this limits your workflow, you can submit feature feedback directly to Microsoft.

Solution 2

Use SUMIFS on the Source Data Instead

If you must keep your PivotTable fields collapsed for presentation purposes, bypass the PivotTable entirely and aggregate the values directly from your raw source data.

Solution 3

Hide Rows Instead of Collapsing Fields

If you simply want a cleaner visual layout without breaking your GETPIVOTDATA formulas, you can manually hide the rows instead of using the PivotTable's collapse feature.

Free Microsoft Office alternative

Experience Powerful PivotTables with WPS Office

If you frequently work with complex datasets and PivotTables, WPS Office provides a lightweight, highly compatible, and completely free alternative to Microsoft Office. Enjoy seamless formula processing, including GETPIVOTDATA, within an intuitive interface for all your data analysis tasks.

1. Download and Install: Download WPS Office for free from the official website and follow the easy installation prompts.
2. Open Your Spreadsheet: Launch WPS Spreadsheet and effortlessly open your existing Excel workbooks containing PivotTables.
3. Analyze Data Seamlessly: Use the familiar PivotTable tools located in the Data tab to summarize, filter, and analyze your information without a steep learning curve.

Fully compatible with Microsoft Excel (.xlsx) formats and PivotTable structures.Advanced data processing capabilities for complex reporting and summary dashboards.Lightweight architecture ensures fast performance even when handling massive datasets.Completely free to use with a familiar, easy-to-navigate spreadsheet interface.

microsoft office alternative - wps office

Frequently Asked Questions

Is there a setting in Excel to make GETPIVOTDATA work with collapsed fields?

No, currently there is no built-in setting or option in Excel that forces GETPIVOTDATA to read items hidden by a collapsed field. The function is strictly designed to query only the visible elements of the PivotTable.

Why does my GETPIVOTDATA return #REF! even when all fields are expanded?

If fields are fully expanded and you still receive a #REF! error, double-check your formula for typos in the item names. Also, verify that the specific item actually exists in the PivotTable data and that the field name hasn't been changed in the source data.

How can I stop Excel from automatically generating GETPIVOTDATA formulas?

If you prefer standard cell references (like =C4) over GETPIVOTDATA when clicking inside a PivotTable, you can disable the feature. Go to the PivotTable Analyze tab, click the drop-down arrow next to 'Options', and uncheck 'Generate GetPivotData'.

Excel & Data Problems