So you have a SPILL error in your Excel tables? In this quick article, let me show you 3 easy fixes to the problem.
Fix 0: See if Excel can auto-fix the formula
This is not really a fix. But if you write certain types of formulas in table, Excel will warn you about the potential spill error and fix it for you. See this example:
One lesson we can take away from this auto-correction of the formula is that if you are using a table column style formula, change it to [@ column] style.
For example:
- Instead of LEFT([Name]) use LEFT([@Name], 1)
- =IF(LEN([Value])>6, “Too long”, “Short enough”) can be =IF(LEN([@Value])>6, “Too long”, “Short enough”)
Note: This auto-correction of formulas is seems to be a new feature, so may not be active in all Excel 365 versions.
Fix 1: Change to a non-spillable formula
If an Excel formula can result in more than one value, it automatically spills. Such formulas are called Dynamic Array Excel Functions. A simple example is the SEQUENCE function.
=SEQUENCE(10)
will return the numbers 1 to 10.
If you use them in a normal cell in Excel, they work ok.
But when you type the same formula in a table, it gives the SPILL error (see this demo).
So an easy fix is to change your formula to a version that doesn’t spill.
Refer to below table to see non-spillable alternatives for some common situations.
| Purpose | Spill Version | Non-Spill Alternative |
|---|---|---|
| To generate row numbers in the table automatically | =SEQUENCE(10) | =ROW()-ROW([#Headers]) |
| Show all matching values with FILTER | =FILTER(data, data=10) | Return only the first matching value: =XLOOKUP(10, data, data) Concatenate all matches into one list: =TEXTXJOIN(“, “,, FILTER(data, data=10)) |
Fix 2: Convert Table to a Range
If you must have a spillable formula in the table column, an easy fix is to convert the table to a range. You will however, loose all the table features (such as structural references, data model connectivity and ability to send data to Power Query).
To convert table to a range, just select the table, go to Table Design ribbon and click on “Convert to Range” button.
Once your table is in a range format,
- Remove any spill formulas in the row 2 onwards in your range
- This will fix the #SPILL! error, as demoed below.
Fix 3: Move the formula outside the table
Tables do not support any sort of spill behavior. So another easy fix is to move the spill formula outside the table to an adjacent column. Something like this:
But what if I need the formula along with my table?
Unfortunately Excel currently doesn’t support having SPILLABLE formulas inside tables. But if you still need a formula result along with your table data (for some calculation purposes), you can use the HSTACK function, like below:
=HSTACT(your_table, spill_formula_here)
For example, I want to add a ID number column to my table. Here is the HSTACK for that:
=HSTACK(my_table, SEQUENCE(ROWS(my_table)))
Why do we even get this error?
It’s not because Excel hates you. There are two things at play here.
- Dynamic Array Formulas: want to spill the formula results down (or sideways) when there is more than value returned by the formula (example: SEQUENCE(10)). They throw a SIPLL error whenever something is preventing the formula from spilling.
- Excel Tables: want to apply the same formula for all cells in the table column.
So when you type an array formula in a table cell, Excel tries to apply the same formula for all cells of the table. This creates a situation where each table row has a formula that wants to return multiple values. So while first row’s formula is trying to spill, second row has a formula of its own (as Excel tables automatically apply the same formula across). Thus the SPILL errors.
Know more about Excel Tables & Dynamic Arrays
Please read below articles to understand Excel Tables & Dynamic Array features of Excel.
