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

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.

- Right-click any cell within the table.
- Next, choose
**Table > Convert to Range**.

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

annoyed with macroshifty

August 14, 2024 at 12:22 pm

You don’t have any examoles of Spill errors where the function returns 2 cells as the answer –

a value for the calculation, and a status value in the following column !

Yes – there are Excel functions that return values for the cell with the function, and for the one in the following column –

even more fun to find when the calculation is in the rightmost column !

Jeff Butts

August 14, 2024 at 1:20 pm

Hmm…didn’t show such an example because I’ve never come across that. Can you share a screenshot or something, so I can try to duplicate it?

annoyed with macroshifty

August 14, 2024 at 3:03 pm

Jeff,

It was a fair while ago that I came across that response –

as the data aggregation was in the last column it was really really annoying – no spill message

when I moved that aggregation to have a couple of helper cells at the end I got to see the error message –

and

the MS help on that function detailed it as always having a status value returned as the value in the column to the right of the formula.

If I can find it I will post into this thread.

Currently trying to find a way to use my windows 10 desktop \Links\ folder with subfolders full of shortcuts to things I use, and sites to reference in the same way but within windows 11,

Windows XP mode where I could hang them all along the top of the screen with autohide would be even better.

annoyed with macroshifty

August 14, 2024 at 3:50 pm

Jeff,

not the one I was commenting about that has the MS note that it always fills 2 adjacent cells – status in the second column one

– but see

For example, when placed in cell E2 as in the example below, the formula =VLOOKUP(A:A,A:C,2,FALSE) would previously only lookup the ID in cell A2 . However, in dynamic array Excel, the formula will cause a #SPILL! error because Excel will look up the entire column, return 1,048,576 results, and hit the end of the Excel grid.

https://support.microsoft.com/en-gb/office/how-to-correct-a-spill-error-ffe0f555-b479-4a17-a6e2-ef9cc9ad4023#:~:text=If%20the%20size%20of%20the,%2C%20RANDARRAY%2C%20and%20RANDBETWEEN%20functions.

and

Excel formulas that return a set of values, also known as an array, return these values to neighboring cells. This behavior is called spilling.

https://support.microsoft.com/en-gb/office/dynamic-array-formulas-and-spilled-array-behavior-205c6b06-03ba-4151-89a1-87a7eb36e531

Looking further,

SUMIFS strikes a string in the memory

https://support.microsoft.com/en-us/office/sum-values-based-on-multiple-conditions-e610ae0f-4d27-480c-9119-eb644f1e847e

and MINIFS is interesting with the

criteria_range2,criteria2, …(optional)

Additional ranges and their associated criteria. You can enter up to 126 range/criteria pairs.

But there is the problem with many business users still using Office 2010.

And I have heard of some “managed” systems providers providing windows Vista and Office 2007, as recently as in 2022, with a £1,000 + charge per PC environment – hardware £1,500+.

I’m currently providing “appropriate levels of support” for systems from win 2010 and office 97 up to win 10 and the base 365 version – no — not a canary. just have a wide yellow streak when it comes to being a tester of beta systems, I prefer better ones !

If users want more – such as win 11 pro, and 365 or other versions such as 2010 pro plus – then they can supply me with a suitably setup system.

I don’t have the gigabit broadband links MS want for virtual systems !

they can pay for that too, if they want !

And do the maintenance to match their corporate systems, advising me when they will be doing updates so I can take new backups, and charge them for the work and facilities involved.

Maybe more looking tomorrow! oor September, or …

bromberg

August 14, 2024 at 12:25 pm

What about OFFICE PRO 2021…will it handle dynamic arrays?

Jeff Butts

August 14, 2024 at 1:19 pm

According to Microsoft, Excel 2021 should support dynamic array formulas and the spilled array behavior discussed here.

Thanks for the question!

dan

August 14, 2024 at 1:31 pm

And thanks for the quick reply!

annoyed with macroshifty

August 14, 2024 at 3:57 pm

But do remember 2021 is a fixed copy of the “production” version of 365, at a certain time.

I may well not get “new features” or functionality.

the scary nights part of providing support for Excel applications –

The deferred printing to your corporate printer – when you get into the office – ha!

Printer has ben removed from the list on your PC.

and why did you set it up as a print now device, not a deferred print server link ?

bromberg

August 14, 2024 at 9:46 pm

I wasn’t aware of that.

Hopefully, someday it will get the Python feature which I understand only O365 has.

Thanks,

Dan