Forum Discussion
SPILL error
Hi. I'm just using a very simple formula to calculate how much money is still due from an original total amount. The formula I have typed is simply in this format: 6000 -(B2-B8), but in the next again cell where I am looking to display the result, I am getting a SPILL error. The correct total is displayed in the cell, but a number of cells immediately below are displaying the "6000" figure!! I'm struggling to correct this - can you help :-) Thank you đź‘Ť
3 Replies
A formula like =6000 -(B2-B8) will most definitely NOT cause a spill error as it does not spill. It's just the same as =6000 - B2 + B8.
However, =6000 -(B2:B8) will spill and could result in an error if there aren't enough blank cells to spill the result into. Is that the formula you are using?
Your formula =6000 - (B2 - B8) is a straightforward calculation that should return just one value. However, in newer versions of Excel (Microsoft 365 and Excel 2021), formulas have dynamic array behavior. This means if Excel thinks your formula could return multiple results, it reserves a range of cells (the "spill range") to display them.
The SPILL error appears when something is blocking that reserved range—in your case, it seems those cells below already contain the value 6000.
Here's how to fix it
1. Clear the cells below your formula
- Click on the cell with the SPILL error.
- You'll see a blue dashed outline around the spill range.
- Select all cells within that outline (below your formula) and press Delete.
- Your formula should now display correctly without the error.
This is the most common fix and usually solves the issue immediately.
2. Check for hidden data or formatting
Sometimes cells look empty but aren't. They might contain:
- A space character
- Invisible formatting
- Old data you've since cleared
Quick fix: Select the spill range, go to Home → Clear → Clear All, and your formula will work perfectly.
3. If you're using a table or structured references
If your data is in an Excel Table and you're using structured references like [@Column], Excel may interpret the formula differently. In that case, try:
=6000 - ([@B2] - [@B8])
Or simply convert the range to a normal range if tables aren't essential for your workflow.
To avoid this in the future:
- Keep the cells below and to the right of your single-value formulas empty
- If you need to copy the formula down, drag the fill handle instead of relying on dynamic arrays
My answers are voluntary and without guarantee!
Hope this will help you.
Was the answer useful? Mark as best response and like it!
This will help all forum participants.
Hello stevia13​,
The SPILL happens because B2-B8 is treated as a range, which creates multiple results. Use =6000 - SUM(B2:B8) to get a single value and stop the SPILL. Make sure the cells below are empty.
