How to use the spill range in Excel

Over the last few months, I’ve written several articles about Excel’s newish dynamic array functions. In many cases, they can replace older, more complex expressions. The new functions do all that calculating internally and return a range of result values. This range of resulting values is called the spill range. When using these dynamic array functions, you’ll need to know all about the spill range, so read on.

SEE: 83 Excel tips every user should master (TechRepublic)

I’m using Microsoft 365 on a Windows 10 64-bit system. These new dynamic array functions are available only in Microsoft 365, Excel for the Web, and Excel for Android tablets and phones. There’s no demonstration file; you won’t need one.

Let’s start with a good definition of spill range

When a dynamic array function or expression produces more than one result value, those values spill over into the adjacent cell(s). In other words, the spill range is the array of values returned by a dynamic array expression or function. It’s important to note that they are merely resulting values and the only cell that contains an expression or function is the cell where you entered it. The top-left cell contains the expression or function and the remaining cells in the spill range display only the resulting values. This means that you have only one cell to alter when modifying the expression or function; however, all the resulting values will update accordingly. Fortunately, it’s not as complicated as it first sounds.

SEE: Windows 11: Tips on installation, security and more (free PDF) (TechRepublic)

When you click any cell within the spill range, Excel displays a blue border around the entire spill range. Everything within that blue boundary is a result value. Figure A shows a small spill range for the UNIQUE() dynamic function in H5. This dynamic function returns a list of unique values from the Region column:

=UNIQUE(Sales[Region],FALSE)

In the screenshot, it’s difficult to tell that the boundary is blue because it’s thin. Currently, the spill range is H5:H7. In addition, the formula bar shows the explicit range instead of using the structured Table referencing. I did so to show both in use; they will both work. 

Figure A

Because the source data is a Table object, the dynamic function in H5 will update as you update the source data in the Table. For instance, if you add a new record that includes a new region, the list beginning at H5 will update accordingly, as shown in Figure B. If you’re not using a Table to store the source data and you enter a value outside the original range (in this case the Region column), the UNIQUE() function spill range will not update. If you delete the dynamic function in H5, the spill range disappears completely.

Figure B

So far, there’s been plenty of room for the result values. What happens when there isn’t?

What is the #SPILL! error?

Although the previous section has been easy—no errors, no problems—that won’t always be the case. Figure C shows a spill range error, #SPILL!. The anchor cell, E5, hasn’t changed; the function is exactly the same. However, instead of seeing the result values we saw earlier, we now see an error.

Figure C

If you guessed that the value in H7, test, is in the way, you’re right! When you see this error, the problem is almost always an inadequate number of cells in which to fulfill the original calculation. If the dynamic function can’t return all of the result values, it returns the #SPILL! error.

The solution is easy. You can delete the offending data in the spill range, or you can move the original dynamic function to a cell where there’s plenty of room to fulfill all of the calculated result values. 

When the problem is obvious, it’s easy to solve. When the problem isn’t as obvious as the example in Figure C, don’t despair. Instead, check the error tag for more information. The error tag is the exclamation point shown when you select the original expression or function (Figure C). Simply click the smart tag to see the error list shown in Figure D.

Figure D

Let’s review the possible errors:

• Spill Range Isn’t Blank is probably the most common error. This is our problem in Figure C. It’s obvious in our example, but what if the problem cell is off screen? When you can’t find the offending value, choose Select Obstructing Cells. Doing so will take you to the cell that’s blocking the result values. At this point, you can decide to delete the value or move the dynamic function.
• Help On This Error opens the Help files with a list of what these error messages mean.
• Select Obstructing Cells will select the cell within the spill range that is blocking the resulting values. It might also indicate merged cells because the spill range can’t accommodate merged cells.
• Show Calculation Steps shows the Evaluate Formula dialog where you can step through the calculations, similar to the way you might step through VBA code in the Visual Basic Editor.
• Ignore Error lets you clear the smart tag and continue working despite the error. I don’t recommend using this one unless you have a specific reason for doing so.
• Edit in Formula Bar gives focus to the actual expression or function in the Formula Bar so you can edit.
• Error Checking Options opens the Options dialog so you can review and possibly change these options.

In our case, which is probably the most common error, the first item, Spill Range Isn’t Blank is direct and informative. If you can’t find the offending cell, choose Select Obstructing Cells. You’ll not often run into the other errors.

In addition, the Table object doesn’t support dynamic array expressions or functions. Don’t enter these expressions and functions into a Table object. The source data can, and should, be in a Table, but don’t enter the actual function into a Table.

Excel’s built-in dynamic array functions are easy to use and easy to maintain as long as you know what the spill range is and how to accommodate its needs.

Read the following articles to learn more about these new dynamic array functions:

Microsoft Weekly Newsletter

Be your company’s Microsoft insider by reading these Windows and Office tips, tricks, and cheat sheets.
Delivered Mondays and Wednesdays

Sign up today

Also see