How to fix #SPILL errors in Excel Tables

This blog post sets out how to prevent or fix #SPILL errors in Excel Tables. Contrary to what Microsoft Help says, they might sometimes be fixable.

#SPILL errors are caused when you have a dynamic array formula which wants to expand (spill) somewhere and cannot.

When searching for solutions to fixing #SPILL errors in Excel Tables, the initial Microsoft help is, er, not very helpful. It just says:

Spilled array formulas aren’t supported in Excel tables. Try moving your formula out of the table, or converting the table to a range (select Table Design > Tools > Convert to range).

It took a bit more searching for me to find this excellent blog post by Mark Proctor, which helps explain why.

Essentially, an Excel Table is already dynamic and auto-expands. So trying to put something else into a Table that is also dynamic and auto-expands is impossible for Excel to deal with.

(It’s well worth reading the rest of the blogpost for tips on how to get dynamic arrays to work with pivot tables and other features).

So, if you are actually trying to do a dynamic array formula, by which I mean one that will expand or contract to fit the data, this just won’t work. So it won’t be possible to include formulas like FILTER or UNIQUE or VSTACK in an Excel Table, sadly.

There are workarounds as per the Microsoft help if this is your scenario.

But I’m not trying to do a dynamic array!

However, the problem I was having the other day was where I wasn’t trying to do a dynamic array. (Or so I thought).

I was trying to use XLOOKUP to bring a mapping from a chart of accounts table into a nominal transaction listing. I didn’t want the data to expand or contract, I wanted the formula to copy down the table as a normal formula might. Something that I thought I’d done hundreds of times before.

But I was getting a #SPILL error.

When I looked more closely at it, and at examples of where I had done this before and it had worked, I spotted the difference.

This was the formula that generated the #SPILL error in my Excel Table:

=XLOOKUP([Account],AccountsMapping[Account Name],AccountsMapping[Overall mapping])

And this was what I needed to do to fix it.

=XLOOKUP([@Account],AccountsMapping[Account Name],AccountsMapping[Overall mapping])

What a difference an @ makes!

Because XLOOKUP is actually a dynamic array formula, if your first input (the thing you want it to look up) is entered as the column data, it will look to treat it as an expandable dynamic array. Which might be useful sometimes, but not on this occasion.

If you enter the @ sign, this tells Excel that you want it to look at one row at a time, and then it will just copy down the formula.

For more technical information about what the @ sign does here (the “implicit intersection operator”), you can read this detailed post by Microsoft.

But what’s missing from their post is that it might be the secret ingredient you need help to solve a #SPILL error.