You see a #SPILL! error when a dynamic array formula cannot display its results. This happens because something is blocking the cells where the results need to appear. The error prevents your formula from calculating correctly. This article explains why the spill range gets blocked and provides clear steps to clear the obstruction.
Key Takeaways: Fixing the #SPILL! Error
- Click the error warning icon: Excel shows a brief description of the specific blockage, such as a table or merged cells.
- Clear the obstructed cells: Delete any text, formulas, or objects in the predicted spill range to resolve the most common cause.
- Move or convert an Excel Table: Dynamic array formulas cannot spill into a Table; you must move the formula or convert the Table to a range.
Why the #SPILL! Error Occurs
Dynamic array formulas, like FILTER, UNIQUE, or SORT, calculate multiple results. Excel needs to place these results in a contiguous block of cells directly below or to the right of the formula cell. This block is called the spill range. If any cell within that predicted range is not completely empty, Excel cannot output the results and shows the #SPILL! error.
A cell is not considered empty if it contains any data, including text, numbers, a formula, or even a stray space character. Formatting like cell borders or fill color does not cause the error. The blockage can also be structural, like part of an Excel Table, merged cells, or a worksheet object that overlaps the spill area. The error is Excel’s way of protecting your existing data from being overwritten by the formula’s output.
Steps to Clear a Blocked Spill Range
Follow these steps to identify and remove the obstruction causing the #SPILL! error.
- Select the cell with the #SPILL! error
Click on the cell containing your dynamic array formula that is showing the #SPILL! error. A small warning icon will appear next to it. - Click the warning icon for details
Click the yellow diamond with the exclamation point that appears to the left of the cell. A menu will open. The first line, such as “Spill range isn’t blank,” tells you the exact type of blockage. - Manually clear the obstructed cells
Excel will highlight the spill range it tried to use with a dashed blue border. Look within this bordered area for any cell with content. Select those cells and press the Delete key to clear them. The formula should now calculate and spill correctly.
Method for Blockages Caused by an Excel Table
If the spill range overlaps with an Excel Table, you must choose one structure or the other.
- Move your formula
The simplest fix is to move your dynamic array formula to a location on the worksheet where its spill range will not intersect with the Table. Cut and paste the formula to a new starting cell. - Convert the Table to a range
If you no longer need the Table features, you can convert it. Click anywhere inside the Table. Go to the Table Design tab on the ribbon. Click Convert to Range in the Tools group. Confirm the action. This removes the Table structure, allowing your formula to spill.
If the #SPILL! Error Persists
Sometimes the obvious blockage is cleared, but the error remains. These are less common causes to check.
Excel Shows #SPILL! Error on an Apparently Empty Range
The spill range may contain a hidden value. A cell could have a formula that returns an empty string (“”) or a space. These are not truly empty. Check for formulas in the spill area and delete them. Also, check for very small objects like a text box or shape that might be overlapping a single cell. Use Home > Find & Select > Selection Pane to see all objects on the sheet.
Spill Range Intersects With Merged Cells
Merged cells cannot be part of a spill range. You must unmerge any cells within the dashed blue border area. Select the merged cells, then go to Home > Alignment > Merge & Center and click Unmerge Cells. Your dynamic array formula should then work.
Formula Spills Beyond Worksheet Boundaries
If your formula is in one of the last rows or columns, the spill range might extend beyond the edge of the worksheet. For example, a formula in cell A1048576 has no rows below it to spill into. You must move the formula to a cell with enough empty space below or to the right to accommodate all results.
Common Blockage Types and Solutions
| Blockage Type | How to Identify | Required Action |
|---|---|---|
| Non-blank cell | Dashed border highlights a cell with text or a number | Select the cell and press Delete |
| Excel Table | Error message states “Spill range intersects part of a Table” | Move the formula or convert the Table to a range |
| Merged cells | Dashed border covers a set of merged cells | Select the cells and use Home > Merge & Center > Unmerge |
| Hidden formula | Cell looks empty but has a formula returning “” or a space | Delete the formula in the obstructing cell |
| Worksheet object | No visible data, but error persists; check Selection Pane | Select the object and delete it, or move it away |
You can now fix the #SPILL! error by clearing data, moving formulas, or adjusting tables. Use the error warning icon for a quick diagnosis of the blockage type. For advanced use, remember that the @ operator can prevent spilling by returning a single value, which is useful when feeding a dynamic array into a cell that cannot handle a spill range.