Ever seen a #SPILL! error in Excel and wondered what’s going on? It usually pops up when a formula that’s supposed to return multiple results can’t display them because something’s in the way. This blockage could be anything – text, merged cells, even a sneaky space character, or simply not enough room to show all the results. The fix is generally straightforward: clear the area or select a truly empty range.
This error typically shows up with dynamic array formulas. These formulas are pretty neat; they output results across multiple cells or an array. Let’s break down why this error happens and how to get around it.
What Triggers a Spill Error?
Dynamic arrays, introduced in 2018, have changed how Excel handles formulas. Now, formulas can handle multiple values and spill those results into several cells automatically. This ‘spilling’ action means a formula entered into one cell can output into a range of cells, called the “spill range”. The size of this range adjusts dynamically based on the data.
If a dynamic array formula tries to spill results but is obstructed, bam! You get the #SPILL error.
Excel has some key functions that use dynamic arrays. These are:
- SEQUENCE
- FILTER
- TRANSPOSE
- SORT
- SORTBY
- RANDARRAY
- UNIQUE
- XLOOKUP
- XMATCH
These dynamic array formulas are exclusive to Microsoft 365 and aren’t supported in earlier offline versions of Excel (like 2016 or 2019).
#SPILL errors aren’t just about data blockages though. There are several reasons why you might stumble upon them. Let’s explore these and how to fix them.
Non-Blank Spill Range
One common reason for a spill error is a non-empty spill range. If your formula is trying to spill ten results, but even one cell in the spill range has data, you’ll get the error.
Example 1:
Let’s say you’re using the TRANSPOSE function to flip a vertical range (B2:B5) into a horizontal one, putting the formula in cell C2. Instead of flipping the column to a row, you get the #SPILL! error.
When you select the formula cell, a blue dashed border will highlight the spill area (C2:F2) and you’ll notice a warning icon.
Click the warning icon to read the error message, which will tell you the spill range isn’t blank.
In this case, cells D2 and E2 have text, which is why the formula can’t spill the results.
Solution:
You can resolve this by clearing or moving any data within the spill range or moving the formula elsewhere.
Clearing the blockage automatically triggers the formula to output the results. Deleting the text in D2 and E2 will transpose the column to a row as needed.
Example 2:
Even if the spill range looks empty, you might still get the Spill error. That’s because there might be an invisible space character in one of the cells.
It’s hard to spot those sneaky characters. To find them, click the error floatie and select “Select Obstructing Cells” from the menu. This will take you directly to the problem cell.
You will see the space characters that are causing the error in the cell. Clearing these invisible characters will allow the formula to work correctly.
Sometimes, the invisible character could be text formatted to match the cell’s fill color, or the cell value is hidden using a custom number code such as ;;;. When you format a cell with ;;;, it hides any content regardless of the font or cell color.
Spill Range With Merged Cells
Another scenario is when the #SPILL! error pops up because the spill range contains merged cells. Dynamic array formulas and merged cells don’t play well together.
Even if the spill range looks empty, like in the example below, the presence of merged cells will cause the spill error.
Check the error details by clicking on the warning sign, which will confirm that the spill range has a merged cell.
Solution:
The easiest way to fix this is to unmerge the problem cells. Select the merged cells, navigate to the “Home” tab, and click “Merge & Center”, then choose “Unmerge Cells”.
If you are facing trouble locating the merged cells, use “Select Obstructing Cells” option from the warning menu to jump to the merged cells.
Spill Range in Table
Dynamic array formulas don’t work directly within Excel tables. They need a single, individual cell to be entered. If you put a spilled array formula inside a table or if the spill area overlaps a table, you will get a Spill error. You can fix this by converting the table to a normal range or moving the formula outside of the table.
When you put a spilled array formula in an Excel table, the error will show up in every cell in the table’s column since Excel copies the formula automatically to all the cells.
You’ll also encounter this if the spill area of a formula runs into a table.
The warning sign will show the message “Spill range in table”.
Solution:
To fix this, convert the table back to a range. You can do that by right-clicking inside the table and selecting “Table” then “Convert to Range”. Alternatively, you can click inside the table, navigate to the “Table Design” tab, and select “Convert to Range”.
Unknown Spill Range
If Excel can’t figure out the size of the spill array, it’ll throw the Spill error. This can happen when the formula is continuously resizing during calculations. It’s common with volatile functions like RAND, RANDARRAY, RANDBETWEEN, OFFSET, and INDIRECT.
For instance, the following formula might produce a spill error because the RANDBETWEEN function keeps returning a different integer between 1 and 500, meaning the SEQUENCE function doesn’t know how many values it needs to output.
=SEQUENCE(RANDBETWEEN(1, 500))
The error message will indicate “Spill range is unknown”.
Solution:
The only way to fix this is to use a different formula.
Spill Range is Too Big
A #SPILL! error can occur if your formula is trying to produce a spill range too large for the worksheet. This can happen when the spill range goes beyond the worksheet’s limits. To fix this, try using references to specific ranges or single cells rather than entire columns, or use the @ character to enable implicit intersection.
Let’s say you are trying to calculate 20% of the sales in column A and return results in column B, however, instead, you get a spill error.
The formula tries to calculate 20% of each value in column A, starting from A3. It wants to output over a million results (1,048,576), which goes beyond the worksheet’s capacity, causing the error.
The error message will say “Spill range is too big”.
Solutions:
Method 1: Use Specific Ranges
Instead of referring to entire columns, try specifying a relevant range in your formula. For example, change the formula to refer to cells A3:A11 instead of A:A.
Method 2: Use Cell Reference and Fill Handle
Replace the entire column with a single cell reference (e.g., A3). Then copy this formula down using the fill handle.
Method 3: Implicit Intersection with @ Operator
Insert the @ operator before the reference to perform implicit intersection. This will display the output in the formula cell only.
Copy this formula from cell B3 to the rest of the range.
Note: When working with a spilled formula, remember that you can edit only the initial cell of the spill range. Other cells in the range will show the formula in gray and can’t be updated.
Out of Memory
If a spilled array formula uses up too much of Excel’s memory, you may get the #SPILL error. In this situation, try referencing a smaller array or range.
Unrecognized / Fallback
You might get a Spill error even when Excel can’t pinpoint the exact reason. In such cases, carefully double-check your formulas and make sure that all the parameters are correct.
That covers the usual reasons why you get the #SPILL error. Hopefully, this helps you fix it when you next encounter it in Excel 365.