**Contents**hide

## What Does SPILL Mean in Excel?

In Excel, SPILL is an error, meaning that a formula tries to fill more than one cell with its result. It usually happens using new functions like FILTER, UNIQUE, or SEQUENCE. When you type one of these formulas into a cell, it can produce an array (a list of values) that spills over into other cells.

If the cells where the formula wants to spill over aren’t empty, you’ll get a SPILL error. To fix it, you need to clear the cells in the range where the formula wants to apply the result values.

In simple words, SPILL means your formula’s result is too big to fit in one cell, and it needs more cells to show the result, but those cells are not empty.

## Quick List of Reasons for SPILL! Error in Excel

Here’s a quick list of some of the reasons why you have the spill error in your worksheet:

**Data Overlap**: There is existing data in the cells where the array formula tries to show its results.**Merged Cells**: The spill range includes merged cells, which cannot take multiple values and stop the function from working.**Table Conflicts**: Arrays cannot spill into Excel tables. If the formula is inside a table and tries to spill outside, it will cause an error.**Unknown Spill Range**: If the range required for the array formula needs to be clarified or specified, Excel will be unable to determine where to place the results.**Array Size Exceeds Limits**: The array returned by the formula is too large for Excel to handle, which can result in an “Out of Memory” error.

## Spill Range is Not Blank

In Excel, you will get the #SPILL! error when you are using a dynamic function that returns the result in a range of multiple cells, but that range already has some values in it, and now that dynamic function can’t overwrite values to return the result in that range. Now to get the result you need to clear values for the range.

In the above example, you can see we are using the RANDARRAY function that returns random values in a specific range of cells that you define. And in the function, I have defined five rows and five columns for the result values which is the range A1:E5.

Now, as you can see in cell B3, I already have a value there and that’s the reason this dynamic formula can’t extend its result and returns the #SPILL! Error.

To deal with this problem, you need to delete the value from cell B3, and the dynamic function (RANDARRAY) expands itself and returns the result in the range A1:E5 which is five rows and five columns.

## Spill Range Error in an Excel Table

If you are using Excel tables, in that case, there’s a chance that you will get the #SPILL! error. The reason for this is that **tables don’t support dynamic formulas yet**.

In the above example, we have Table1 where we are trying to get the random number in one column and ten rows, but as we are using the table, it has returned a spill error.

As per Microsoft, there’s no support yet on Excel Tables for the dynamic functions. The way out is to convert the table into a normal range or move your formula to a normal range.

And when you are converting your table into a normal range make sure to delete and re-enter the formula, otherwise it will still show you the error for all the values other than the last value.

When you convert the table into a normal range Excel tries to expand the dynamic formula to the other cells, but those cells still have the spill error (other than the last cell with the formula) and that’s why the dynamic formula won’t be able to get the result for you.

## #SPILL! Error Problem with VLOOKUP

There’s might a situation where you get the spill error with the VLOOKUP. Well, it’s not common to get this error with the VLOOKUP, but there is a chance to get it. To understand it, you need to understand the situation and how you are using the formula in the wrong way.

In the above formula, in the lookup value instead of referring to a single cell I have referred to the entire column and this makes it a dynamic formula.

Now as I have referred to the entire column where I have a total of 1048576 cells, and with the dynamic formula Excel will return 1048576 results. Correct?

**Now the problem is** to get the 1048576 results I need to have 1048576 cells, but I am short by one cell as my formula starts from the D2.

And when I use the same formula from the first cell of a column, I get all the formulas accurately.

## Merge Cell can also Cause a #SPILL! Error

If you have a merged cell in the spill range while entering a dynamic formula, in that case also, you will get the spill error in the result.

In the above example, we have a merged cell for the range B2:C3 and when we try to enter RANDARRAY in cell A1, it returns the spill error in cell A1 as it can’t re-write a result in a merged cell.

## Selecting Obstructing Cells to Fix the #SPILL! Error

When you click on the cell where you have the #SPILL! error, it gives a drop-down where you have a few options to deal with it.

On the third position, it shows an option to select the obstructing cells, cells that block the result. And the moment you click on this option it will select all the cells that have value.

If you have multiple cells, it will select all those cells and you can delete the values from those cells to get the result in the range that is defined in the formula.

## Use @ for Implicit Intersection

When you use a dynamic function that uses a spill range (return result in multiple cells), For avoiding the #SPILL! error, you can use @ operator at the start of the formula to make the formula return results in a single cell instead of an array for multiple cells.

- Original Formula: =RANDARRAY(5,5,1,100)
- Modified Formula: =@RANDARRAY(5,5,1,100)

And if your formula refers to a range that causes a spill, use @ to limit it to a single value.

- Original Formula: =A2:A10
- Modified Formula: =@A2:A10

## May Be Out of Range

When you have an “Out of Memory” #SPILL! Error, the formula tries to create an array too large for Excel to handle with the available memory. In this case, try to limit the data your formula uses. Instead of applying the formula to a large range, try using it on a smaller part of your data.

Free up memory by closing other applications running on your computer. Or remove any unnecessary data, worksheets, or Pivot Tables in your workbook to free up memory.