When you create a dynamic array formula in Excel, the results spill into adjacent cells. If those cells are hidden by a filter, a collapsed group, or manual row/column hiding, the formula returns a #SPILL! error. This happens because Excel cannot write the spilled values into cells that are not visible on the screen. This article explains why hidden cells block spilling and provides step-by-step methods to fix the error.
Key Takeaways: Unblock Spilled Arrays in Hidden Cells
- Unhide rows or columns: Revealing the blocked cells lets the array spill correctly and removes the
#SPILL!error. - Select a spill range that avoids hidden areas: Move the formula to a location where the entire spill range is visible.
- Use the @ operator to force single-result behavior: Converts the dynamic array into a single-cell formula that does not require spilling.
Why Hidden Cells Block Dynamic Array Spills
Dynamic array formulas in Excel, introduced in 2020, automatically return multiple results that “spill” into adjacent cells. The formula is written in one cell, and Excel fills the neighboring cells with the output array. For this to work, every cell in the spill range must be empty and visible.
When a cell in the spill range is hidden due to filtering, grouping, or manual hiding, Excel cannot write the result there. The formula engine treats hidden cells as blocked and returns the #SPILL! error instead. The error message says “Spill range is not empty” or “Spill range is hidden,” depending on the version of Excel. The root cause is always the same: Excel cannot place data into a cell that is not currently displayed.
This behavior is by design. Excel prevents writing into hidden cells to avoid data being overwritten without the user noticing. Once the hidden rows or columns are unhidden, the array spills normally. The fix is to either make those cells visible or to modify the formula so it does not need to spill into the hidden area.
Methods to Fix Spill Errors Caused by Hidden Cells
Choose one of the following methods depending on whether you want to keep the data hidden or move the formula. Each method resolves the #SPILL! error.
Method 1: Unhide the Rows or Columns in the Spill Range
The simplest fix is to make all cells in the spill range visible. Follow these steps:
- Identify the spill range
Click the cell with the dynamic array formula. Excel draws a blue border around the intended spill range. Note the row and column references of that range. - Unhide rows
Select the rows above and below the hidden rows in the spill range. Right-click any selected row number and choose Unhide from the context menu. Repeat for all hidden row blocks inside the spill range. - Unhide columns
Select the columns to the left and right of hidden columns in the spill range. Right-click a selected column letter and choose Unhide. - Remove filters
If the spill range is inside a filtered table or range, clear the filter. Go to the Data tab and click Clear in the Sort & Filter group. Alternatively, press Ctrl+Shift+L to toggle the filter off. - Expand collapsed groups
If rows or columns are grouped, click the + button above the group to expand it. You can also go to the Data tab and click Ungroup in the Outline group to remove the grouping entirely. - Check for the #SPILL! error to disappear
After unhiding all cells, the formula recalculates and shows the full array.
Method 2: Move the Formula to a Location Without Hidden Cells
If you need to keep certain rows or columns hidden, move the formula to a part of the worksheet where the entire spill range is visible.
- Select a new cell for the formula
Choose a cell where the spill range does not overlap any hidden rows or columns. To verify, temporarily unhide all rows and columns, note the intended spill range, then rehide only the areas that fall outside the new spill range. - Copy the formula
Press Ctrl+C on the cell with the error. Then press Escape to exit copy mode. - Paste into the new cell
Select the new cell and press Ctrl+V. The formula recalculates. If the new spill range has no hidden cells, the error disappears. - Delete the original formula
Select the original cell and press Delete to remove the formula that still shows the error.
Method 3: Use the @ Operator to Return a Single Value
If you only need one result from the array formula, the implicit intersection operator (@) forces the formula to return a single value instead of spilling. This method changes the behavior of the formula, so use it only when you do not need the full array of results.
- Edit the formula
Click the cell with the#SPILL!error and press F2 to enter edit mode. - Add the @ operator
Place the cursor immediately after the equal sign and type@. For example, change=SORT(A1:A10)to=@SORT(A1:A10). - Press Enter
The formula now returns only the first value from the array. The spill range is no longer needed, so the error disappears. - Verify the result
Check that the returned value matches the expected single result. If you need the full array, do not use this method.
If the Spill Error Persists After Unhiding
The Spill Range Still Shows #SPILL! After Unhiding All Cells
If you have unhidden all rows, columns, cleared filters, and expanded groups but the error remains, one of the cells in the spill range may contain data or a merged cell. Select the entire spill range by clicking the cell with the formula and then pressing Ctrl+Shift+Right Arrow and Ctrl+Shift+Down Arrow. Look for any cell that is not empty. Clear the contents of those cells by selecting them and pressing Delete. Also check for merged cells in the spill range. Select the spill range, go to the Home tab, click Merge & Center, and choose Unmerge Cells.
The Error Returns After Reapplying a Filter
If you clear a filter to fix the error and then reapply the filter, the spill range may become hidden again. To prevent this, move the formula to a row outside the filtered range. For example, if your data is in rows 1 through 100 and you filter by a column, place the dynamic array formula in row 102 or below. The spill range then occupies rows that are not affected by the filter.
The Error Occurs Only on a Specific Version of Excel
Dynamic arrays are available in Excel for Microsoft 365, Excel 2021, and Excel 2024. Older versions like Excel 2019 or earlier do not support dynamic arrays. If you open a workbook containing dynamic array formulas in an older version, Excel may convert them to static arrays or show errors. To check your version, go to File > Account > About Excel. If you are using an unsupported version, upgrade to Microsoft 365 or Excel 2024 to use dynamic arrays without issues.
Unhide vs Move Formula vs @ Operator: Key Differences
| Item | Unhide Cells | Move Formula | @ Operator |
|---|---|---|---|
| Effect on data | Reveals hidden rows or columns | Keeps hidden rows or columns unchanged | Returns a single value instead of an array |
| Spill range required | Yes, full array spills | Yes, but in a new location | No, no spill range needed |
| Best for | You can unhide the cells | You must keep some data hidden | You need only one result from the formula |
| Formula change | None | None, only location changes | Adds @ before the function name |
Now you can resolve #SPILL! errors caused by hidden cells in your dynamic array formulas. Start by checking whether the spill range contains hidden rows, columns, or filtered data. If unhiding is not an option, move the formula to a clear area. For single-value outputs, use the @ operator. To prevent future errors, place dynamic array formulas in worksheet areas that are never hidden or filtered.