How to fix #SPILL! in Microsoft Excel

Microsoft Excel offers features that make managing data in spreadsheets easy. However, from time to time, it also throws an error.

#SPILL! is an error that occurs even when the formula has syntax and is used under the right conditions. If you get an error #SPILL! in a worksheet cell, do the following.

What is #SPILL error in Excel?

The first step to fixing the #SPILL error in Excel is to understand the condition that generated it. Microsoft Excel is used with various formulas, which unlock many powerful features. A style called array formulas applies the function to a range of cells instead of just one cell.

In newer versions of Excel, array formulas automatically expand to fit the space required for formula operation. This is called the spill range. In the spill range, other related cells that do not directly contain the array formula related to the formula are grayed out in the formula bar.

An array formula needs enough space to 'spill over' the data, so these cells are not available or interrupted, so the formula cannot function properly. This is the most common cause of the #SPILL! Whenever an array formula is executed in a range that overlaps the existing data, the #SPILL error will occur.

Also you also face this error for the same reason if overflow area includes merged cells, htwowcs array size is not fixed value.

How to fix #SPILL! in Excel

This error may sound 'difficult', but it is quite simple to deal with once you know the cause of the error. Fortunately, Excel will tell you what's causing the problem right next to the formula. All you need to do is check this box and click the warning sign next to it. Once the cause has been identified, you can take steps to fix #SPILL! in Excel.

If there is an obstruction in the overflow range, you should first attempt to move or delete the data that interferes with the formula. Alternatively, you can move an array formula to an area that has enough space for the overflow.

If the merged cells interfere with the overflow of the array, you need to find and unmerge that cell. You can do this by selecting the merged cell and clicking Merge and Center > Unmerge Cells on the Home tab.

Another type of #SPILL! occurs when Excel cannot calculate and match the absolute size of the output value. Normally, you wouldn't need a function that returns billions of values. In such cases, the formula itself can cause this problem. You need to edit the formula so it outputs the appropriate, much smaller array.

If you can't find any other reason why the formula isn't working, double-check the entered formula to make sure all the required arguments in the function are entered correctly.

After the array formula has enough space to work again, it will automatically recalculate. Now you can get back to work without any other problems. If the formula is not automatically recalculated, it may have been calculated manually initially. You can force it to recalculate by pressing F9 on your keyboard.

Above are the simplest ways to fix #SPILL! in Excel . Hope the article is useful to you.

Kareem Winters

Update 28 July 2023