A #SPILL! error in Excel occurs when a dynamic array formula generates multiple output values but cannot write them to the worksheet because an obstacle is blocking the spill range. When that obstacle is a merged cell, the calculation engine halts immediately. Dynamic arrays require completely unmerged, individual grid cells to populate their output. If even a single merged cell sits inside the required spill corridor, Excel cannot allocate the results and displays #SPILL!.
How Do You Fix a #SPILL! Error Caused by Merged Cells?
To resolve a
#SPILL!error caused by merged cells, select the formula cell, click the warning indicator to view the dashed spill boundary, locate the merged cells within that boundary, and click Unmerge Cells on the Home tab. Alternatively, replace merged display headers with Center Across Selection to keep the visual alignment without breaking dynamic arrays.
Why Dynamic Arrays Cannot Populate Merged Cells
Modern Excel functions like FILTER, UNIQUE, SORT, and SEQUENCE are dynamic arrays. Instead of returning a single value into a single cell, they calculate an entire result set in memory and spill the data downward and outward across adjacent rows and columns.
Each value in a dynamic array requires an addressable, standard single cell. A merged cell combines two or more individual cell coordinates into a single visual block while discarding the underlying grid references for all but the top-left cell. Because the calculation engine cannot split an array value across irregular cell geometries, any merged cell inside the destination area creates a structural conflict that triggers #SPILL!.
For a comprehensive diagnostic map of all dynamic array failure modes, consult Troubleshooting #SPILL! and #CALC! Errors: The Dynamic Array Diagnostic Guide.
Step-by-Step Diagnostic: Isolating the Merged Blockage
When managing large worksheets, merged cells are often hard to spot visually, especially when empty or formatted to blend into the worksheet background.
Step 1: Reveal the Spill Outline
- Select the cell containing the
#SPILL!error. - Look at the grid. Excel draws a thin, dashed border around the exact range of cells the formula is trying to fill.
- If any cells within that dashed box have missing gridlines, you are looking at a merged cell blockage.
Step 2: Use the Error Indicator Menu
- Click on the error cell.
- Click the yellow warning icon that appears next to the formula cell.
- The menu displays the specific reason: Spill range contains merged cells.
- Click Select Obstructing Cells. Excel automatically highlights the exact merged range causing the collision.
Practical Fixes for the Merged Cell Conflict
Depending on how your worksheet is formatted and whether you need to preserve existing visual layouts, choose one of the following remediation methods.
Method 1: Unmerge the Cells Directly
The most direct solution is to remove the merge formatting from the obstructing range:
- Select the merged cells flagged by Excel.
- Go to the Home tab on the Ribbon.
- In the Alignment group, click the Merge & Center toggle to unmerge the cells.
- Once the cells return to standard grid geometry, the dynamic array will automatically spill into the cleared space.
Method 2: Center Across Selection (The Professional Alternative)
If you merged cells to create a wide visual header across multiple columns, you do not need to sacrifice your presentation to accommodate dynamic arrays. Excel includes a built-in formatting feature that provides the same visual centering without merging the grid coordinates:
- Unmerge the problematic header cells.
- Select the range of horizontal cells across which you want the label centered (for example,
B2:E2). - Press
Ctrl + 1to open the Format Cells dialog box. - Navigate to the Alignment tab.
- In the Horizontal dropdown menu, select Center Across Selection.
- Click OK.
Your text will display centered across the entire block, but every individual cell remains independent. A dynamic array formula starting above or nearby can now calculate through that row without triggering a #SPILL! error.
Hidden Complications and Secondary Checks
If you unmerged the cells and the #SPILL! error persists, your worksheet likely contains a secondary barrier.
Non-Empty Values Behind Unmerged Cells
When you unmerge cells, data that was previously hidden under the merged structure might remain in the secondary cells. Dynamic arrays will not overwrite existing data. If text, numbers, or invisible spaces occupy any cell in that path, review #SPILL! Error: Non-Empty cells in the spill range (The “Ghost” character) to clear remaining obstructions.
Protection and Sheet Locks
If the worksheet is protected, Excel may block formulas from unmerging cells or recalculating across locked ranges. If your formula fails to expand in an administrative file, see #SPILL! in Protected Worksheets: Range lock issues.
Using Merged Cells in Source Lookup Data
Merged cells do not just disrupt dynamic array outputs; they also disrupt traditional search algorithms. If your dynamic array calls a lookup formula that scans a table built with merged blocks, see #N/A in Lookup Tables with Merged Cells.
Limiting Output with Implicit Intersection
If you did not intend for the formula to return an array across multiple cells, you can force Excel to return only the single value on the current row by using the @ operator. To understand how this works, see #SPILL! vs. The Implicit Intersection Operator (@).