A #SPILL! error in Excel occurs when a formula is unable to populate cells with the calculated results.
the formula in cell D1 uses the SORT function to sort the fruit table in cells A1:B4 by number of fruit.
A similar formula in cell D7 that aims to sort the table in cells A7:B10 produces a #SPILL! warning, as cell D9 is already populated with the word ‘text’ which prevents the output of the SORT formula from showing.
Cases when SPILL! error can happen:
- The formula is returning more results than can fit in the specified range.
- The specified range is blocked by other data or objects.
- The formula is using a volatile function, which means that it is recalculated every time the worksheet is changed. This can cause the size of the array to change, which can lead to a #SPILL! error.
To fix a #SPILL! error:
- Increase the size of the specified range.
- Delete or move any data or objects that are blocking the specified range.
- Use a non-volatile function instead of a volatile function.
You can read more about specific cases of SPILL! error on the microsoft website