How to Prevent #SPILL Errors in Excel Tables  

Bottom Line: Learn a couple of ways to prevent errors when using dynamic array formulas that produce spill ranges in Excel Tables.

Skill Level: Intermediate

Watch the Tutorial

Watch on YouTube & Subscribe to our Channel

Download the Excel Files

You can follow along with the tutorial using the files below. I've included both the Before and After Excel files.

Why You Might See a #SPILL Error in Your Excel Table

#SPILL errors occur when there essentially isn't enough space for Dynamic Array Functions to spill their results. (For an explanation of these functions and how they work, see this post: Dynamic Array Formulas & Spill Ranges.)

For example, below I'm using a FILTER function in an Excel Table, and it is returning #SPILL errors in column C. That's because the formula is designed to look up a customer ID on another sheet and return the phone number associated with it. The problem comes in when there is more than one phone number. The spill range is larger than the single cell that the Excel Table allows for that formula value.

Spill Error Reason

If this function were written outside of an Excel Table, the spill range would expand and show the multiple results. But because this is in an Excel Table, the formula gets copied down to the cells below. This essentially blocks the spill results.

You can see that some entries don't have a #SPILL error. That is because there is only one phone number associated with that particular customer.

Workarounds for the #SPILL Error

There are a couple of different ways to take care of this problem.

1. Combine the Values Using TEXTJOIN

We can wrap our existing FILTER function with the TEXTJOIN function. This will combine the multiple results into one cell, separated by a delimiter (a character or mark of our choice).

The first argument to identify for the TEXTJOIN function is the Delimiter. You can use a comma, a space, a slash, a pipe, or whatever you think would work best. Enclose your delimiter in quotation marks when you are typing out this portion of the formula. Also, be sure to include one or more spaces before/after the delimiter depending on how separated you want your results to be from one another.

I find that with lots of numbers like these, the comma doesn't quite separate the entries visually as well as something like space, the pipe symbol, and another space: " | "

The next argument is Ignore Empty, which means the formula will skip over entries that have nothing in them. Set that argument to true by just typing the word true.

The last argument is Text, and that is the array that the FILTER function is returning. In other words, the FILTER formula that already exists becomes the final argument for the TEXTJOIN function.

All together, our formula looks like this:

TEXTJOIN Formula with Arguments identified

With our formula complete, the text is combined together (yet separated with delimiters) for any entry that has more than one result.

TEXTJOIN RESULTS

One of the drawbacks of this solution is that because multiple results are combined into a single cell, it makes it difficult to analyze or further work with the individual entries. You might want to use formulas or pivot tables to take a closer look at some of the results, but that can be challenging if they are all combined together.

That leads us to our next solution.

2. Split Values into Their Own Cells Using INDEX

Just as with the previous solution, we are going to keep the FILTER formula as an argument in our new INDEX function. In this case, it will be the first argument for INDEX, which is Array.

The second argument is Row Number. This is the row number that we want to return from the filter's spill range. You can type the number 1 to indicate the first row of your spreadsheet, but I prefer to make it a little more dynamic. Instead of typing 1, I can select a cell that is in Row 1 and then add a dollar symbol ($) in front of the row number reference (but do not add a dollar symbol in front of the column letter reference). Example: C$1

This makes my row reference absolute and my column reference relative so that the values calculate correctly when copied in cells to the right.

You do not need to identify any other arguments for this formula, so the final result looks like this.

INDEX function with arguments identified

Hit Enter. The formula will copy down, and each of the cells in the column will show the first phone number for each customer. As you copy the formula in columns to the right, the second and third phone numbers will appear, if applicable.

Additional phone numbers are displayed in their own cells.

Removing the #REF! Errors

If you don't like seeing those #REF! errors, you can wrap your entire formula in the IFERROR function, adding quotation marks with nothing in between them to indicate you want nothing to appear in those cells.

IFFERROR function

How many columns do I need?

A small drawback to this solution is that you have to figure out how many extra columns to add to your table. A relatively easy way to do this is to go to your source data and add a column where you can enter a COUNTIF function. The COUNTIF function will tally any duplicate Customer IDs and return the number of times that ID occurs in the data.

COUNTIF returns the number of cells within a range that meet a given condition.

From there, if your data set is relatively small, you can glance down the list to see the highest number. That would be the number of columns you need for your table. If your data set is large, you can use the MAX function to quickly tell you what the highest value is.

MAX returns the largest value in a set.

Related Posts

Take a look at these tutorials on related topics:

Conclusion

These solutions will work for any Dynamic Array formula that returns a spill error. It's not specific to the FILTER function.

I hope they are helpful to you. If you have any other comments, tips, or questions in regards to spill ranges, I'd love to hear them in the comments below.

Until next time!

2 comments

Your email address will not be published. Required fields are marked *

  • Hi Jon, do you know of a way to overcome the spill error while still keeping the returned index values in rows rather than columns? Your index solution is elegant, however is difficult to implement as I have certain values that require 20 columns. Perhaps you know of a workaround, besides manually copy-pasting the formula to avoid spill? Thanks!

Generic filters
Exact matches only

Excel Shortcuts List

keyboard shortcuts list banner

Learn over 270 Excel keyboard & mouse shortcuts for Windows & Mac.

Excel Shortcuts List

Join Our Weekly Newsletter

The Excel Pro Tips Newsletter is packed with tips & techniques to help you master Excel.

Join Our Free Newsletter