How-To

Resolve a Spill Error in Excel Like a Pro Using These Tips

By Jeff Butts

Published August 13, 2024

Are you trying to use dynamic arrays in Excel but keep getting a spill error? We’ll show you how to fix that pesky problem.

The newest versions of Microsoft Excel feature dynamic arrays. Using these arrays, you can enter a formula, like to calculate totals or averages of a group of numbers, into Excel just once. Next, multiple value outputs will “spill” into neighboring cells. This is different from using the fill handle on your formula cell in that Excel automatically populates the neighboring cells with the calculated information. There are times when this fails to work as expected, though, leaving you with a frustrating spill error in Excel. Follow along to learn how you can get your formula back on track and populate your spreadsheet cells with the data you need.

Most Common Things That Break a Dynamic Formula and Result in a #SPILL! Error

When you encounter a #SPILL! error in Microsoft Excel, it’s usually because the formula isn’t able to populate multiple cells with the results the spreadsheet has calculated. The problem is almost always that something is preventing Excel from filling in the cells where you want your data, called the spill range.

There are a few causes for this, and Excel does its level best to let you know what’s blocking the cells from being populated.

Clicking the yellow triangle warning icon will generally show one of these Excel spill errors:

• Spill range isn’t blank
• Spill range contains merged cells
• Spill range in table
• Spill range is unknown
• Spill range is too big

The Easiest Problem: Spill Range Isn’t Blank

Probably the easiest problem to solve is the “Spill range isn’t blank” error. Excel won’t overwrite data in your cells, so see if one of the cells in your spill range already has a value.

To do this, start by clicking the formula cell. Excel will display the spill range with a dashed border around it. In the simplest case, you’ll immediately see which cell isn’t empty, and you can clear it.

That might not help if the spreadsheet is really big or if there are invisible characters (like a space), making one of the cells non-empty. Fortunately, Excel can help. Just click Select Obstructing Cells from the error dialog, and Excel will highlight the non-blank cells.

As soon as you empty the cell or cells causing the error, you’ll see your data fully populated.

How to Fix Merged Cells in the Spill Range

Next, you may have inadvertently merged cells together within your spill range. If this is the case, the error dialog will state, “Spill range has merged cell.” You’ll need to unmerge the cells for the dynamic formula to spill its values through the range of cells.

The easiest way to find the merged cells is to click Select Obstructing Cells from the error dialog. Once done, you can click the Merge Cells button in the ribbon to unmerge the cells. Once that’s done, your data should display as you want it to.

What to Do If Spill Range is in a Table

For some reason, known only to Microsoft, dynamic formulas and tables within Excel spreadsheets don’t get along. If you try to populate cells in a table using a dynamic formula, it will fail. You’ll need to either display the output elsewhere or convert the table to a range.

If you need to convert a table to a range, just follow these steps.

1. Right-click any cell within the table.
2. Next, choose Table > Convert to Range.
   
3. Confirm you want to convert the table to a range by clicking Yes.
   

Assuming you don’t have any other errors with your spill range, your dynamic formula should succeed in populating the appropriate cells with data.

How to Resolve an Unknown Spill Range

Next up is a tricky error, “Spill range is unknown.” This error happens when Excel isn’t able to determine what size it should set for the array. This usually happens when you are using a volatile function like RANDARRAY, RAND, or RANDBETWEEN in your dynamic function.

Essentially, such a randomized function results in the array changing between the spreadsheet’s calculations. The only way to resolve this will be to work out a different formula that doesn’t change the array’s size between calculations.

How to Fix a Spill Range Is Too Big Error in Excel

Finally, you might be used to using a formula that calculates, for example, a percentage of each cell in a column. For example, a formula such as =B:B*10% would work perfectly fine in Excel 2019 and earlier.

In newer versions of Excel that support dynamic formulas, that same formula will generate a “Spill range is too big error.” This is because, with dynamic formulas, Excel attempts to calculate all of the values for the array at once. As a result, it reaches the end of the spreadsheet before it’s done outputting the values.

To get around this, your best solution will be to either refer to a range of cells instead of a column or avoid the use of a dynamic formula altogether. For example, changing the above formula to =B2:B12*10% will work just fine.

You can also stick to the traditional way of populating your formula down the range of your column, either by copying and pasting the formula or using the autofill handle in the cell containing your formula.

Making the Most of Dynamic Formulas and Spill Arrays

Microsoft introduced dynamic arrays in Office 365 in January 2020. It’s supported in Excel 365 for Windows, Mac, Apple, Android, and Windows Mobile. Notably, you’ll find that Excel Online does not support spilling or dynamic arrays.