#SPILL! error in Excel
A dynamic array formula has more than one result, and something is sitting where the extra results need to go.
What #SPILL! means
#SPILL! belongs to the modern, dynamic-array Excel. Functions like SORT, FILTER, UNIQUE and SEQUENCE return a whole block of values from one formula, and Excel fills the neighboring cells automatically. That filling is called spilling, and the cells it needs are the spill range.
The error appears when the spill range is not clear. Excel never overwrites your data, so it stops and flags the formula instead. The cause is therefore not in the formula at all: the formula is right, and the sheet has something in the way.
That makes #SPILL! one of the easier errors to fix once you know where to look. The formula cell shows a dashed outline of the intended range, and the warning icon beside the cell gives the exact reason.
Causes of #SPILL! and how to fix each
1. Data in the cells the result needs
Functions such as SORT, FILTER and UNIQUE return a block of values that spills down and right from the formula cell. If any cell in that block already holds something, even a single stray character, the whole formula returns #SPILL! rather than overwriting it.
| A | B | |
|---|---|---|
| 1 | 3 | #SPILL! |
| 2 | 1 | note |
| 3 | 2 |
Fix. Click the error cell. A dashed blue outline shows where the result wants to go, and the warning icon beside the cell offers Select Obstructing Cells. Clear or move whatever is in the way, and the result spills straight away. Do not paste values into the spill range.
| A | B | |
|---|---|---|
| 1 | 3 | 1 |
| 2 | 1 | 2 |
| 3 | 2 | 3 |
With B2 and B3 empty, SORT spills the three values in order. The formula lives only in B1, and Excel fills the cells below it.
2. Merged cells in the spill range
Dynamic arrays cannot spill into merged cells. A header that was merged across several columns and happens to sit under the formula is enough to block it.
Fix. Select the merged cells and choose Home, Merge and Center to unmerge them. Use Center Across Selection in Format Cells if you want the same look without merging.
3. The result would run off the sheet or into a Table
A spill range cannot cross the last row or column of the worksheet, and it cannot spill into an Excel Table. A formula that returns a whole column of results, such as one that references an entire column, can hit the edge. Putting the formula inside a Table cell triggers the same error.
Fix. Move the formula outside the Table, and point it at a bounded range such as A2:A500 instead of an entire column. If you only need one value from the result, use INDEX on the array instead of letting it spill.
Trapping #SPILL! in a formula
Leave #SPILL! untrapped. It is about the sheet layout, not the data, and IFERROR would only paper over a formula that is not showing its results. Fix the layout. If you intend to use the array formula inside another function that needs only one value, such as SUM or INDEX, the result no longer spills and the error does not occur.
#SPILL! on Excel job tests
If a FILTER, SORT or UNIQUE task starts with data already below the formula cell, a #SPILL! means the range is blocked, not that the formula is wrong. Look at the dashed outline and clear the range.
Older Excel versions never show this error, because they do not have dynamic arrays. If the prompt says the workbook is for Excel 2016 or 2019, use the older approach instead: a helper column, or a formula confirmed with Ctrl+Shift+Enter.
Related guides
#SPILL! questions
- What does #SPILL! mean?
- A formula returns several values, but the cells where they should appear are not empty. Excel will not overwrite them, so the formula shows
#SPILL!instead. - How do I find what is blocking a spill range?
- Click the cell with
#SPILL!, open the warning icon beside it and choose Select Obstructing Cells. Excel highlights every cell in the way. - Can I use dynamic arrays in older versions of Excel?
- No. Dynamic arrays arrived in Microsoft 365 and Excel 2021. Excel 2019 and earlier need array formulas confirmed with Ctrl+Shift+Enter.
Practice working with errors
The free 5-item sampler uses the same kind of formulas in a working grid, and it shows you the error value when a formula goes wrong.
Try a 5-item Excel test sample