fbpx
Search
Close this search box.

How to fix SPILL Error in Excel Tables (3 easy solutions)

Share

Facebook
Twitter
LinkedIn

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:

Excel auto correcting the SPILL error in tables

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

Spill error in Excel Tables - quick 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.

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

convert table to a range using Table Design Ribbon in Excel

Once your table is in a range format,

  1. Remove any spill formulas in the row 2 onwards in your range
  2. This will fix the #SPILL! error, as demoed below.
Spill error will go away after you convert a table to range.

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:

Moving the dynamic array formula (spillable) outside the table resolves it!

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.

Facebook
Twitter
LinkedIn

Share this tip with your colleagues

Excel and Power BI tips - Chandoo.org Newsletter

Get FREE Excel + Power BI Tips

Simple, fun and useful emails, once per week.

Learn & be awesome.

Welcome to Chandoo.org

Thank you so much for visiting. My aim is to make you awesome in Excel & Power BI. I do this by sharing videos, tips, examples and downloads on this website. There are more than 1,000 pages with all things Excel, Power BI, Dashboards & VBA here. Go ahead and spend few minutes to be AWESOME.

Read my storyFREE Excel tips book

Overall I learned a lot and I thought you did a great job of explaining how to do things. This will definitely elevate my reporting in the future.
Rebekah S
Reporting Analyst
Excel formula list - 100+ examples and howto guide for you

From simple to complex, there is a formula for every occasion. Check out the list now.

Calendars, invoices, trackers and much more. All free, fun and fantastic.

Advanced Pivot Table tricks

Power Query, Data model, DAX, Filters, Slicers, Conditional formats and beautiful charts. It's all here.

Still on fence about Power BI? In this getting started guide, learn what is Power BI, how to get it and how to create your first report from scratch.

Leave a Reply