How to Fix #SPILL! Errors in Excel – Step‑by‑Step Guide
1. What the #SPILL! Error Means
#SPILL! appears when a dynamic‑array formula (e.g., FILTER, SORT, SEQUENCE) cannot write its full result to the worksheet. Excel 365 and Excel 2021 introduced these “spilling” functions, which automatically expand into adjacent cells. If anything blocks the intended spill range, Excel replaces the result with #SPILL! and shows a small green triangle with a tooltip that explains the cause.
2. Common Reasons a Spill Fails
| # | Cause | Typical symptom | Quick check / fix |
|---|---|---|---|
| 1 | Obstructing data – any non‑empty cell (value, text, or formula) inside the spill area | Tooltip says “Blocked by cell(s)”. | Click the error cell; a faint blue border outlines the spill range. Delete or move the data inside that border. |
| 2 | Merged cells intersecting the spill range | Tooltip says “Merged cell”. | Unmerge the cells (Home ► Merge & Center ► Unmerge) or move the formula elsewhere. |
| 3 | Excel Table boundaries – a formula inside a Table cannot spill outside it | Tooltip says “Spill area overlaps a Table”. | Convert the Table to a normal range (Ctrl + T → Convert to range) or place the formula outside the Table. |
| 4 | Insufficient worksheet size – the array would extend past the last row or column | Tooltip says “Spill range exceeds sheet size”. | Reduce the array size (e.g., limit rows with TAKE) or move the formula higher/left. |
| 5 | Overlap with another spill – two dynamic‑array formulas trying to occupy the same cells | Tooltip says “Spill range overlaps another spill”. | Relocate one of the formulas so each spill has its own area. |
| 6 | Legacy Excel version – workbook opened in a version that lacks dynamic‑array support | Tooltip says “Spill not supported”. | Open the file in Excel 365/2021 or replace the formula with a legacy array formula (INDEX/SMALL). |
3. How to Identify the Spill Range
- Select the cell showing #SPILL! – the formula bar displays the full array formula.
- Look for the blue border that appears around the intended spill area.
- If the border encloses only blank cells, the formula will work once any hidden obstruction is cleared.
- If the border includes a filled cell, delete or move that content.
- If the border is broken or missing, check for hidden rows/columns, protected sheets, or merged cells.
Tip: Press Ctrl + Shift + Enter only for legacy (CSE) array formulas; modern dynamic‑array formulas do not require it.
4. Fixing #SPILL! by Adjusting the Formula
Sometimes the layout is fine, but the formula itself returns more data than can fit. Limiting the output or coercing the data type often resolves the issue.
| Problem | Fix | Example |
|---|---|---|
| Too many rows/columns | Use TAKE, INDEX, or CHOOSECOLS to trim the result. |
=TAKE(FILTER(A2:C20, D2:D20>0), 10, 3) returns only the first 10 rows and 3 columns. |
| Mixed data types | Coerce to a single type with TEXT, VALUE, or the unary --. |
=FILTER(--A2:A10, ISNUMBER(--A2:A10)) keeps only numeric values. |
| Implicit intersection (legacy formula returning a single value) | Wrap the legacy expression in a dynamic‑array function. | =FILTER(A2:A10, B2:B10="Yes") replaces a single‑cell IF that caused a spill. |
Sample Fix with INDEX
Original formula (spills the whole column):
=SEQUENCE(ROWS(A2:A100))
If you need only the first 5 numbers, wrap it with INDEX:
=INDEX(SEQUENCE(ROWS(A2:A100)), SEQUENCE(5))
Sample data
| A |
|---|
| 10 |
| 20 |
| 30 |
Result – the formula returns a vertical array {1;2;3;4;5} that fits in five cells.
5. Using SEQUENCE and FILTER Safely
Both functions generate arrays that can easily exceed the available space. Follow these checks before you let them run.
| What to check | Why it matters | Quick fix |
|---|---|---|
| Empty destination range | Any hidden text, a formula, or even a formatted‑but‑blank cell blocks the spill. | Select the intended range → Delete (or Clear All) to ensure it’s truly empty. |
| Table boundaries | Dynamic arrays cannot spill into a Table. | Convert the Table to a normal range or move the formula outside the Table. |
| Array‑size limits | SEQUENCE can generate up to 1,048,576 rows × 16,384 columns – the worksheet limit. |
Keep arguments realistic, e.g., =SEQUENCE(1000,5). |
| FILTER returning no rows | Returns #CALC!, not #SPILL!, but a wrapper may cause a spill error. |
Provide a fallback: =FILTER(A2:B20, B2:B20>100, "No match"). |
Example: Safe SEQUENCE + FILTER
=FILTER(SEQUENCE(5,1,10,2), SEQUENCE(5,1,10,2) <= 18)
| A |
|---|
| 10 |
| 12 |
| 14 |
| 16 |
| 18 |
The formula creates a sequence {10;12;14;16;18} and then filters it, producing a 5‑row spill that fits in the adjacent cells.
6. Advanced Troubleshooting (No VBA Required)
| Issue | Diagnosis | Fix |
|---|---|---|
| Dynamic arrays disabled (Excel 2019 or earlier) | Enter =SEQUENCE(3). If you get #NAME?, the engine isn’t available. |
Upgrade to Excel 365/2021, or replace with a legacy CSE formula (=ROW(INDIRECT("1:3"))). |
| Implicit intersection forcing a single‑cell result | Type =A2# next to a Table; if only one value appears, intersection is active. |
Turn off File → Options → Formulas → “Use legacy array formulas” or set calculation to Automatic. |
| Hidden rows/columns or protected sheet | No blue border appears, or it stops abruptly. | Unhide rows/columns (Home ► Format ► Hide & Unhide) and unprotect the sheet (Review ► Unprotect Sheet). |
| Underlying error masked by #SPILL! | Wrap the formula in IFERROR: =IFERROR(yourFormula, "Error: "&ERROR.TYPE(yourFormula)). |
Resolve the reported error (e.g., divide‑by‑zero, negative SEQUENCE rows). |
7. Preventing Future Spill Errors
- Reserve a clean spill area – Insert blank rows/columns where you expect arrays to appear.
- Avoid merged cells in regions where you’ll use dynamic arrays; they block spills.
- Clear contents, not just formatting, before placing a spill formula. Select the range → Delete → Clear All.
- Use error‑handling functions to give users a friendly message instead of #SPILL!
| Function | When to use | Example |
|---|---|---|
IFERROR |
Return a custom note when the spill can’t occur. | =IFERROR(SORT(A2:A10), "Not enough space") |
LET |
Store the spill result in a variable, then reference it only where needed, reducing accidental overwrites. | =LET(res, FILTER(A2:A20, B2:B20>0), res) |
@ (implicit‑intersection operator) |
In older workbooks, force a single‑cell result to avoid a spill. | =@SORT(A2:A10) returns only the first sorted value. |
- Check workbook settings – Ensure Enable iterative calculation is off unless required, and that Allow dynamic arrays is enabled (File → Options → Advanced).
FAQ
What does the #SPILL! error mean in Excel?
It indicates that a dynamic‑array formula cannot write its full result to the worksheet because something (data, merged cells, a Table, or the sheet edge) blocks the intended spill range.
How can I quickly find the spill range causing the error?
Select the cell with #SPILL!; Excel draws a faint blue border around the cells the formula wants to fill. Any non‑blank cell inside that border is the blocker—delete or move it, or adjust the formula to a smaller range.
Can I use the same fix in Google Sheets?
Google Sheets also supports dynamic arrays, but it displays a different error (#REF! or “Array result was not expanded because it would overwrite data”). The same principles apply: ensure the destination range is empty, avoid merged cells, and limit the array size with functions like INDEX or ARRAY_CONSTRAIN. The exact formulas are identical for most functions (FILTER, SORT, SEQUENCE).