How to Fix Array Result Not Expanded #REF! Errors in Excel and Google Sheets
Question details
The user needs to fix a #REF! error that occurs because an array formula cannot expand without overwriting existing data.
- Product
- Excel and Google Sheets
- Device & OS
- not provided
- Scenario
- Using dynamic array formulas or importing a Google Sheets template into Excel, typically on a Summary sheet calculating cross-sheet data.
- Observed behavior
- The cell displays a #REF! error with a message indicating that the array result was not expanded because it would overwrite existing data.
Confirm whether the file was originally built in Excel or Google Sheets, as dynamic array functions and cross-sheet references behave slightly differently between the two applications.
Clear Data from the Intended Spill Range
Remove any text, formatting, hidden characters, or formulas that are blocking the array from expanding into neighboring cells.
Dynamic array formulas automatically populate multiple cells, known as a 'spill range'. If even a single cell in this intended range contains data (including a hidden space), the formula returns an error to prevent accidental overwriting.
Select the cell containing the #REF! error. A faint dashed blue border will appear, outlining the exact range the formula is attempting to fill.
Check the highlighted area for any rogue data, spaces, or invisible formatting that might be occupying the required cells.
Highlight all empty cells within the dashed border, right-click, and select 'Clear Contents', or simply press the Delete key on your keyboard.
Once the range is completely empty, the array formula will automatically recalculate and populate the spilled data.
Unmerge Cells in the Destination Range
Dynamic array formulas cannot spill into merged cells and require standard individual cells to display properly.
Fix Corrupted INDIRECT Formula References
Correct sheet references that may have broken during conversion between Google Sheets and Excel.
Easily Manage Dynamic Arrays with WPS Office
WPS Spreadsheet fully supports dynamic array functions and cross-platform compatibility, making it easy to fix spill errors and open complex Google Sheets or Excel templates without breaking formulas.
- 1. Open your workbook: Launch WPS Spreadsheet and open your existing Excel or Google Sheets template.
- 2. Click the error cell: Select the cell with the array formula to instantly view the required spill range outlined on your sheet.
- 3. Clear and calculate: Use the clear contents tool to remove any obstructing data and watch WPS Spreadsheet instantly process your dynamic arrays.
Frequently Asked Questions
Why do I get a #REF! error instead of a #SPILL! error?
In Google Sheets, blocked array formulas typically return a #REF! error stating the result was not expanded. In modern Microsoft Excel, this exact same scenario returns a #SPILL! error. If you are seeing #REF!, you are likely using Google Sheets or an older Excel file format.
Can I force an array formula to overwrite existing data?
No, spreadsheet applications intentionally prevent array formulas from overwriting existing data to protect your information. You must manually clear the required cells or move the formula to an area with enough empty space.
Why does my downloaded Google Sheets template show errors in Excel?
Google Sheets and Excel handle certain complex array configurations and the INDIRECT function slightly differently. When you export a Google Sheets file to .xlsx, some syntax might not translate perfectly, requiring you to manually update the sheet references in Excel.
How do I find invisible characters blocking my formula?
Select the entire blank range where the formula should expand and press the Delete key. Often, cells that appear empty actually contain invisible spaces, apostrophes, or cleared formatting from a previous template design.
