Your Excel formula returns a #SPILL! error instead of showing results. This happens when a dynamic array formula cannot output its results into the required cells. The spill range is blocked by existing data or a structural issue in the worksheet.
Dynamic array functions like FILTER, SORT, and UNIQUE need empty cells to display their results. A conflict prevents Excel from using those cells. This article explains why spill errors occur and provides specific steps to clear the blockage.
Key Takeaways: Resolving #SPILL! Errors
- Clear the spill range: Delete any visible or invisible content in the cells where the formula needs to output data.
- Check for merged cells: Unmerge any cells within the anticipated spill area, as they block the dynamic array.
- Convert the table to a range: Excel tables do not support dynamic array spill ranges directly within their structure.
Why the #SPILL! Error Appears
A #SPILL! error indicates a physical obstruction. The formula itself is often correct, but its intended output destination is unavailable. Excel calculates the result of a dynamic array function and determines how many cells it needs. This required area is the spill range.
If any cell in that calculated range contains data, a formula, a merged cell, or is part of an Excel Table, the spill fails. The error is a protective measure to prevent overwriting existing information. Common blockages include typed values, legacy array formulas, hidden characters from a copy-paste operation, or even a single cell with a space character.
How Excel Determines the Spill Range
Excel evaluates the formula in the top-left cell where you type it. It then looks down and to the right to populate the spill range. The size is dynamic and changes if the source data changes. The spill range is treated as a single entity. You cannot edit part of it, only the original formula cell.
Steps to Clear the Spill Range Blockage
Follow these steps in order to identify and remove the obstacle causing the #SPILL! error.
- Click on the cell with the #SPILL! error
Excel displays a small warning icon. Hover your mouse over this icon. A tooltip appears, stating the specific reason for the error, such as “Spill range isn’t blank.” - Select the obstructed cell from the tooltip
The tooltip often includes a button labeled “Select Obstructing Cell.” Click it. Excel will navigate to and select the cell that is blocking the spill. If the tooltip does not have this button, manually examine the cells directly below and to the right of your formula. - Clear the contents of the blocking cell
With the obstructing cell selected, press the Delete key on your keyboard. This removes any visible data, formulas, or invisible characters like spaces. - Check for and unmerge cells in the spill area
Go to the Home tab. In the Alignment group, look at the “Merge & Center” button. If it is highlighted, the selected cells are merged. Click the dropdown arrow next to it and choose “Unmerge Cells.” - Convert an Excel Table to a regular range if needed
If your formula is inside a Table, right-click any cell in the Table. Choose Table > Convert to Range from the context menu. Confirm the action. Dynamic array formulas can now spill next to or within this converted range.
Using the Go To Special Feature
For persistent errors where the blockage is not obvious, use a broader cleanup method.
- Select the entire potential spill area
Click the cell with the formula. Then, while holding Shift, use the arrow keys or mouse to select a large area below and to the right where results should appear. - Open the Go To Special dialog
Press F5 on your keyboard to open the Go To dialog. Click the “Special…” button at the bottom. - Select Constants and Formulas
In the Go To Special dialog, first select “Constants” and click OK. Press Delete to clear any static values. Repeat the F5 > Special process, but this time select “Formulas” and click OK. Press Delete to clear any legacy formulas.
If the #SPILL! Error Persists
After clearing visible content, the error might remain due to less common issues.
Excel Crashes or Freezes When a Spill Error is Present
A complex circular reference involving a dynamic array can cause instability. Open the Excel Options dialog via File > Options > Formulas. In the Calculation options section, set workbook calculation to “Manual.” Close and reopen the file. This may break the calculation loop and allow you to delete the problematic formula.
Spill Range Extends Beyond Worksheet Boundaries
If your formula returns thousands of rows, the spill range might hit the last row of the sheet. Excel cannot spill beyond row 1,048,576. You must revise your formula logic, perhaps by using the FILTER function with a criteria that limits the number of returned rows.
Conflict with Legacy CSE Array Formulas
Older workbooks may contain array formulas entered with Ctrl+Shift+Enter. These occupy a multi-cell range and block new dynamic arrays. Find these by pressing F5 > Special > Current array. Delete the entire legacy array formula by selecting all its cells and pressing Delete.
Dynamic Array Spill Behavior vs. Manual Data Entry
| Item | Dynamic Array Spill Range | Manually Entered Data |
|---|---|---|
| Data Source | Linked to a single formula in the top-left cell | Independent values or formulas in each cell |
| Editing | Only the original formula cell can be edited; spill range is locked | Any individual cell can be edited or deleted |
| Size Adjustment | Automatically expands or contracts with formula results | Fixed size; must be manually adjusted |
| Overwrite Protection | #SPILL! error prevents overwriting existing content | No protection; new data overwrites old data |
| Compatibility | Requires Excel for Microsoft 365 or Excel 2021 | Works in all versions of Excel |
You can now identify and clear the obstructions causing #SPILL! errors in your worksheets. Start by using the built-in tooltip to select the obstructing cell for a quick fix. For more complex sheets, use the Go To Special method to clear a whole area. Remember that converting a Table to a range is often necessary for formulas that need to spill inside a data set. Use the new dynamic array functions with the knowledge that they require a clear output path to work correctly.