How To Solve the #SPILL Error in Excel [Easy 2024 Guide]

Even experienced Excel users occasionally encounter puzzling errors that can disrupt their workflow. One such error is the dreaded #SPILL error in Excel.

This error can be frustrating, especially when working with complex formulas and large datasets. Thankfully, resolving the #SPILL error is manageable. In this article, we’ll show you exactly how to fix it. With our step-by-step guide, discover how easy it is to fix SPILL Excel errors — in just a few simple clicks!

What Is the SPILL Error in Excel?

The “#SPILL” error message in Excel indicates that a formula has produced more results than the target range can accommodate.

If the adjacent cells aren’t empty — or the formula spills over into already occupied cells — Excel displays the “#SPILL” error. This alerts you that the formula’s output cannot be properly displayed.

A Note on Dynamic Arrays and #SPILL Errors

The “#SPILL” error was introduced with Excel’s dynamic array feature, which allows formulas to #SPILL their results into neighboring cells automatically.

It enables users to perform calculations on arrays of data without the need for complex array formulas or manually copying formulas across a range. While this feature enhances the efficiency and flexibility of calculations, it also introduces the possibility of encountering the “#SPILL” error.

With dynamic arrays, a single formula can generate multiple values — and automatically populate the adjacent cells — based on output size. This feature simplifies tasks such as filtering, sorting, and performing calculations on large data sets.

Dynamic arrays in Excel encompass several functions that leverage this capability, such as FILTER, SORT, UNIQUE, and SEQUENCE. These functions can take an array or a range of cells as input and return an array of results that automatically spills into the surrounding cells.

What Causes the #SPILL Error?

There are many reasons why a formula might return the #SPILL error in Excel. These different reasons all have different solutions.

The causes for the #SPILL error in Excel include the following:

1. The #SPILL range is not blank
2. The #SPILL range has a merged cell
3. The #SPILL range is too extensive
4. The #SPILL is in a table in Excel
5. The #SPILL range is unknown (often due to a volatile function)

6 Methods for Fixing the #SPILL Error in Excel

Resolving the Excel #SPILL error either requires adjusting the target range or modifying the formula to ensure the output fits within the designated cells.

1. When the #SPILL Range Isn’t Blank

You’ll encounter the #SPILL error when the Excel formula needs to return results in cells that aren’t all empty.

There are two ways to solve the error if the range isn’t blank. When using the following two methods, values in the range won’t interfere with the results of the formula. This effectively removes the #SPILL error.

Clear the #SPILL Range

To clear the range, you can:

• Select each cell with a value in the range and press “Backspace.”
• Select the range, then cut and paste it to another location.

Once you’ve cleared the range, go to the cell with the formula, double-click on it, and press “Enter.”

Move the Formula to a Blank Range

1. Select the cell with the formula.

2. Right-click on it. Choose “Cut” or use the Windows keyboard shortcut Ctrl + X (Cmd + X on macOS).

3. Click on a cell in a different column. Paste the Windows keyboard shortcut Ctrl + V (Cmd + V on macOS).

Note: Sometimes, cells may look blank but have a formula or hidden value.

2. When the #SPILL Range Is in the Table

Unfortunately, dynamic arrays don’t work in table formats in Excel. Therefore, you may spot a #SPILL error if your data has been formatted to a table.

.

To solve this, convert your table into a range. Here’s how:

1. Select the table.
2. Right-click inside the table.
3. Go to “Table” > “Convert to Range.”

• If you spot #SPILL at the bottom of the range, you can cut and paste them to the top.

• Another option is to move the formula outside of the table by copying and pasting the formula next to the table.

3. When the #SPILL Range Is Too Large

The #SPILL error results from a formula with a too-large range. Maximum #SPILL range size depends on the Excel version being used. In older versions of Microsoft Excel, this was never a problem: Before the dynamic arrays update, formulas would not automatically #SPILL over to other cells. However, newer versions often return a #SPILL error if the range is too extensive.

In Excel 365 and Excel 2019, the limit is approximately 17.2 billion cells. Earlier versions of Excel have more restrictive limits (but are still capable of dynamic arrays).

This typically happens when selecting an entire column as your range within the formula.

In this example, we want to add 1 to the values in column C. However, we’ve used the range C: C (which is why the formula returned a #SPILL error).

To solve this, we can use the actual value range instead of the whole column:

1. Type the equals sign (=) in the cell.
2. Select the range of data for the formula.

3. Add the rest of the formula. (For our example, we used +1. However, you can use a variety of different formulas) and press “Enter.”

Note: The formula works better with a smaller defined range than the entire column.

Using the Fill Handle for Large #SPILL Ranges

Another way to solve this is to work with one cell first:

1. Write the equals sign (=) in the cell.
2. Select one cell as the formula range.

3. Add the rest of the formula.

4. Press “Enter.”

5. Copy the formula to the rest of the cells by clicking and dragging the small square at the bottom-right corner of the cell.

With this method, it doesn’t matter how big the range is: You can copy the formula to the rest of the cells and eliminate the #SPILL error.

Using Implicit Intersections

One final way to solve the problem of the #SPILL range being too large is to apply “implicit intersection.” With the help of Excel’s implicit intersection function, you can use a formula to get a single value out of a group of cells. The value intersecting with the formula cell’s row and column is automatically chosen whenever there is an implicit intersection.

To apply it, add the @ symbol to your formula in front of the range as follows:

4. When the #SPILL Range Has Merged Cells

Merged cells are also an obstacle to dynamic arrays (which return the #SPILL error). As you can see,  a merged cell in the column for sorted data is an obstacle to the formula.

The easiest way to solve this problem is by unmerging the cells:

1. Click on the merged cells. You can also select the entire range or column.
2. Go to “Merge & Center” > “Unmerge Cells.”

If you have trouble with merged cells, click on the yellow icon beside the error and choose “Select obstructing cell.” This will show you the cell that’s causing the #SPILL errors, indicating where to start troubleshooting.

Related: Excel vs. Google Sheets

5. When the #SPILL Range is Unknown

A “range is unknown” issue may be caused when the formula cannot define the size or range of the output. This can happen for a variety of reasons. As a result, the #SPILL range becomes ambiguous.

This can be caused by a number of issues, such as:

Circular References

Excel cannot establish the precise range of the output if the formula involves circular references. A circular reference occurs when the formula refers to its own cell — or depends on another cell that indirectly links back to it. Circular references may cause the calculation to fail and result in a #SPILL.

Volatile Functions

When a workbook is changed, certain Excel functions (referred to as volatile functions) recalculate. The values returned by these operations, like NOW(), RAND(), and TODAY(), fluctuate often. This can make identifying the #SPILL range tricky.

Array Formulas

The output range may not be explicitly defined if the formula uses an array formula, which operates on numerous cells simultaneously. Since array formulas frequently provide several outcomes, Excel must choose the proper #SPILL range. However, if the array formula contains contradictions or certain complexities, the range can become ambiguous and result in the #SPILL error.

In our example, we used the following formula:

 =SEQUENCE(RANDBETWEEN(1,100))

Using the formula above would return a #SPILL error.

Because the RANDBETWEEN function is volatile, the SEQUENCE function will return an unknown array.

When the #SPILL range is unknown, the only way to solve the error is by performing calculations with other non-volatile functions.

Related: Converting Excel to Google Sheets

Frequently Asked Questions

What Are the Easiest Ways to Solve #SPILL Errors in Excel?

There are two general methods to solve the #SPILL Excel error — and avoid cell obstacles:

• Clear the #SPILL range of all values and formatting
• Insert the formula into a different (empty) range

How Do I Turn off SPILL Errors in Excel?

To turn off #SPILL errors, put the symbol “@” before the formula that’s producing the error. The implicit intersection functionality will be enabled, automatically combining numerous values into one value.

How Do I Fix the VLOOKUP #SPILL Error?

Your VLOOKUP formula may return a #SPILL error if you have used an entire column as the data range. Try working with a more definite range in the formula to remove the error.

Your results from an array VLOOKUP function may also not have room to fit in the cell range. Try performing the search in a different part of the spreadsheet.

What Is the #SPILL Error in Excel SUMIF?

The #SPILL error in Excel SUMIF either occurs when there’s an obstacle in your #SPILL range or the range in the formula is too big. It also occurs in formulas like SORT, UNIQUE, TRANSPOSE, and VLOOKUP.

Try using the formula in a section of the spreadsheet that has more empty cells for the results to #SPILL into. You can also alter the range in your formula to only include the exact cells you’re using (i.e., C2:C15 instead of C: C).

Bottom Line

A number of different factors can cause the #SPILL error in Excel. It can usually be fixed by removing existing content from cells (or merged cells) that are in the way.

Now that you know what a #SPILL error in Excel is — and all the possible causes — you should have everything you need to fix them.

If you found this article useful, be sure to check out our premium spreadsheet templates. When you use the promo code “SSP,” you’ll save 50%!

Get Premium Templates

Related:

• How To Count Unique Values in Excel [Guide]
• Guide: Excel IF Statements with Multiple Conditions
• 4 Easy Examples of Greater Than or Equal To in Excel
• How To Add Leading Zeros in Excel [Easy Guide]
• How To Randomize a List in Excel [Simple Guide]