30 Advanced Excel Tips for 2026

Excel Champs / Excel Formulas List / #SPILL! Error in Excel (Understanding and Fixing)
By Puneet Gogia (Microsoft MVP)

#SPILL! Error in Excel (Understanding and Fixing)

30+ Advanced Excel Tips for 2026

No spam. Only Excel Stuff. God Promise. 😊

#SPILL! means a dynamic array formula — FILTER, UNIQUE, SEQUENCE, RANDARRAY, SORT — has more results than it can place, because something is blocking the cells it needs.

The usual cause is data already sitting in the spill range: click the error icon and choose Select Obstructing Cells to jump straight to whatever is in the way, then clear it. Four other causes account for nearly everything else. Merged cells inside the spill range block it, since a merged cell can’t take multiple values.

Excel Tables can’t hold spilled results at all, so the formula has to move outside the table or the table needs converting to a range. A formula referencing a whole column (=VLOOKUP(A:A,...)) tries to return over a million results and runs out of sheet.

And a volatile function inside the formula can make the spill size unpredictable, giving “spill range is unknown”. Once it works, you can reference the whole result elsewhere with the spill operator: =SUM(D2#).

What Does SPILL Mean in Excel?

In Excel, #SPILL! is an error you get when a formula tries to fill more than one cell with its result but can’t. It usually happens with the new dynamic array 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 size of the array keeps changing because the formula uses a volatile function (like RANDBETWEEN inside SEQUENCE), Excel can’t determine how many cells the result needs.
  • Array Size Exceeds Limits: The array returned by the formula would go beyond the edge of the worksheet, or it’s too large for the available memory, which shows as an “Out of Memory” #SPILL! 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 the dynamic function can’t overwrite those values to return the result. To get the result, you need to clear the values from that range.

spill-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.

value-in-spill-range

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, there’s a chance that you will get the #SPILL! error. The reason for this is that tables don’t support dynamic formulas yet.

spill-range-error-in-table

In the above example, we have Table1, where we are trying to get random numbers 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.

delete-and-re-enter-formula

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 might be a situation where you get the spill error with 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.

spill-error-with-vlookup

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. It’s like Microsoft’s own example of this error:

=VLOOKUP(A:A,A:C,2,FALSE)

As the lookup value is a whole column, the formula tries to return one result for every row of the worksheet.

Now, as I have referred to the entire column, where I have a total of 1048576 cells, the dynamic formula 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 D2.

vlookup-formula

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

But returning a million results is slow and not what you want in most cases. The better fixes are to refer only to the cells with lookup values, to refer to a single cell and copy the formula down, or to add @ in front of the column so it uses only the value from the same row:

=VLOOKUP(A2:A10,A:C,2,FALSE)
=VLOOKUP(A2,A:C,2,FALSE)
=VLOOKUP(@A:A,A:C,2,FALSE)

The first one spills nine results, the second one returns one result that you copy down, and the third one works like the old implicit intersection.

Merge Cell can also Cause a #SPILL! Error

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

merge-cell-cause-spill-error

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 write a result in a merged cell. To fix it, unmerge the cells or move the formula.

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.

select-obstructing-cells

In this menu, you have the “Select Obstructing Cells” option, which selects the cells that block the result. The moment you click on this option, it selects all the cells that have values in the spill range.

cell-already-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.

multiple-cells-having-value

Use @ for Implicit Intersection

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

use-@-for-implicit-intersection
  • 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 Memory

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.

Spill Range Is Unknown

This one comes from volatile functions. When a dynamic array function gets its size from a volatile function like RANDBETWEEN, the size of the result changes every time Excel calculates, so Excel can’t work out how many cells it needs. Microsoft’s example is this formula:

=SEQUENCE(RANDBETWEEN(1,1000))

To fix it, give the function a fixed size, or calculate the size in another cell and refer to that cell.

Refer to a Spill Range with the # Operator

Once a dynamic formula works, you can refer to its whole result by adding # after the first cell. For example, if a UNIQUE formula in D2 spills a list, the following formula counts the items in that list, and it updates when the list grows or shrinks:

=COUNTA(D2#)

Frequently Asked Questions

Which Excel versions show the #SPILL! error?

Only versions with dynamic arrays: Excel for Microsoft 365, Excel 2021 and later, and Excel for the web. Excel 2019 and earlier don’t spill results, so you won’t see this error there.

How do I stop a formula from spilling?

Refer to a single cell instead of a range, or add the @ operator in front of the range or function, as shown above. Then the formula returns one value in one cell.

Can I hide the #SPILL! error with IFERROR?

Not really. IFERROR can show a different value in the formula cell, but the result still can’t spill. The only real fix is to remove what blocks the spill range.

Puneet Gogia

Written by

Puneet Gogia

Microsoft MVP — Excel · Founder, Excel Champs

Twelve years of teaching Excel, and a decade before that using it as a data analyst. Every tutorial here is written from a file I actually built.

3M+ readers helped
1,000+ tutorials written
Since 2015 teaching Excel

Leave a Comment