If your Excel formula that references another cell shows a #SPILL! error, it means the formula is trying to return multiple values into a range that already contains data, or the spill range is blocked. The fix is usually to clear the obstructing cells or change the formula to return a single value.
What Is a Spill Range and Why Does It Happen?
In modern Excel (Microsoft 365 and Excel 2021), dynamic array formulas can return multiple results that automatically “spill” into adjacent cells. For example, =A1:A5 entered in cell C1 will spill down to C1:C5. The #SPILL! error appears when something blocks that intended output range. The most common cause is that the cells where the results would go already contain data, merged cells, or a table structure.
When you write a simple formula like =B2 in cell C2, it normally returns just one value. But if you use a range reference like =B2:B10, Excel treats it as a dynamic array and tries to spill. If any cell in the spill area (C2:C10) is not empty, you get #SPILL!.
How to Fix the #SPILL! Error
Here are the steps to resolve the error:
- Click the cell with the #SPILL! error. Excel draws a dashed border around the intended spill range.
- Look for any non-empty cells within that dashed border. Clear them (select and press Delete) or move their contents.
- Check for merged cells in the spill range – unmerge them (Home > Merge & Center).
- Ensure the spill range is not inside an Excel Table. Tables cannot contain spilled array results; convert the table to a range (Table Design > Convert to Range) or move the formula outside the table.
- If you only want a single value, change the formula to reference a single cell, e.g.,
=B2instead of=B2:B10.
After clearing the blockage, the formula will recalculate and show results.
Common Causes of Spill Blockages
Beyond simple data in cells, several other situations cause #SPILL!:
- Merged cells: Any merged cell in the spill area blocks the array.
- Table structures: Excel Tables have implicit structured references that conflict with dynamic arrays.
- Volatile functions: Functions like
RANDARRAYorSEQUENCEalways spill, so they need empty space. - Formulas in the spill area: Even formulas that return blank strings (“”) count as content.
- Protected sheets: If the sheet is protected, Excel may not allow writing to the spill range.
How to Choose Between Single Cell and Spill
If you don’t need multiple results, avoid range references. For example, to copy a value from another cell, use =A1 (single cell). If you need to extract a list, consider using FILTER or SORT which are designed to spill. For older Excel versions (2019 or earlier), dynamic arrays don’t exist, so range formulas would need to be entered as legacy array formulas with Ctrl+Shift+Enter – but that’s rare now.
To intentionally use spill, make sure the target area is completely empty. You can also use the @ operator to force implicit intersection, e.g., =@A1:A5 returns the value in the same row as the formula.
What If You See #SPILL! in a Simple Formula?
Sometimes even =B2 can show #SPILL! if B2 itself is a spilled array. For instance, if B2 contains =SEQUENCE(3), it spills to B2:B4. Then a formula in C2 that references B2 will return the whole spilled array, causing a spill in C2:C4. To get just the first value, use =@B2# (the @ for implicit intersection, # for the spill reference).
Comparison: Excel 365 vs Older Versions
Dynamic arrays are only available in Excel for Microsoft 365, Excel 2021, and Excel 2024. In Excel 2019 and earlier, range formulas behave differently: they return a single value based on implicit intersection, or require Ctrl+Shift+Enter for array formulas. If you share a workbook with someone using an older version, your spilled results will appear as #VALUE! or as legacy arrays. To avoid compatibility issues, you can use INDEX to return a single value, e.g., =INDEX(A1:A5,1).
Frequently Asked Questions
Why does my formula show #SPILL! when I only reference one cell?
If the referenced cell itself contains a spilled array, your formula inherits the array. Use =@B2# to get just the first value, or change the original formula to not spill.
How do I clear a spill range quickly?
Select the cell with #SPILL!, press Ctrl+G (Go To), click Special, choose “Current array” (if available), then press Delete. Alternatively, manually select the dashed border area and delete.
Can I use a spill formula inside an Excel Table?
No, Excel Tables do not support spilled arrays. Convert the table to a range (Table Design > Convert to Range) or place the formula outside the table.
What does the #SPILL! error mean on a Mac?
The same as on Windows. The fix is identical: clear the blocking cells. The keyboard shortcut for Go To is Cmd+G on Mac.
Is there a way to make a formula return only one value from a range?
Yes, use INDEX (e.g., =INDEX(A1:A5,1)) or the implicit intersection operator @ (e.g., =@A1:A5).
Preventing Spill Errors in the Future
To avoid #SPILL! errors, plan your layout: keep spill areas clear, avoid merging cells in data regions, and be cautious when using dynamic array functions near other content. If you frequently work with arrays, consider using LET to define intermediate results and reduce spill complexity. Also, use the # operator to reference spill ranges explicitly, which makes formulas more readable.
Related
- Excel Choose Date from Calendar Cell: How to Add a Date Picker
- Excel Center Jobs: What They Are & How to Get One
- Excel Center Kokomo Indiana: What It Is & How to Enroll
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Errors & fixes
- Large Excel File Keeps Crashing? Practical Fixes to TryIf a large Excel file keeps crashing, first make a copy of it, then open Excel in Safe…
- Microsoft Excel: How to Unmerge Cells and Fix LayoutsTo unmerge cells in Excel for Microsoft 365 on Windows, select the merged cell or range, then choose…
- Find Option Not Working in Excel? Causes and FixesIf Find is not working in Excel, press Ctrl+F, open Options in the Find dialog, and check the…
- Excel Dates Sorting A-Z: Why It Happens and How to FixExcel sorts real dates A to Z in chronological order, from oldest to newest. If the dates look…
- Excel Error Bars for Each Point: Set Custom ValuesTo give each point in an Excel chart a different error bar, select the chart, choose Chart Design…
- Hyperlink Not Working in PowerPoint? Fix the Common CausesIf a hyperlink is not working in PowerPoint, first test it in Slide Show view: in editing view,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.