How to use Excel’s spill range

Excel’s dynamic array functions are responsible for Excel’s spillage range. These functions are used to create the spill range.

Image: PixieMe/Shutterstock

In the past few months, I have written several articles on Excel’s dynamic array functions. They can often replace more complicated expressions. These new functions perform all the calculations internally and return a variety of results. The spill range is the range of values returned by these functions. You will need to be familiar with the spill range when using dynamic array functions.

SEE: 83 Excel tips every Excel expert should know (TechRepublic).

Microsoft 365 is installed on a Windows 10 64bit system. These dynamic array functions can only be used in Microsoft 365, Excel For the Web, Excel for Android Tablets and Phones. You won’t require a demonstration file.

Let’s start by defining spill range.

A dynamic array function or expression that produces more than one value will cause those values to spill over into the cell(s) next. In other words, the dynamic array expressions or functions return an array of values that is called the spill range. They are not the resulting values. The only cell that contains an expression/function is the one where it was entered. The spill range contains only the resulting values, while the top-left cell has the expression or function. This means you only have one cell to modify when you change the expression/function; however, all of the resulting values will update accordingly. It’s not as difficult as it sounds.

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

Excel displays a blue border that surrounds the entire spillage area when you click on any cell within it. All cells within this blue boundary are a result value. Figure AShows a small spillage range on the UNIQUE()dynamic function in H5. This dynamic function returns a list with unique values from the region column.

=UNIQUE(Sales[Region],FALSE)

The screenshot doesn’t show the blue boundary. It is thin. At the moment, the spillage range is H5 to H7. Additionally, the formula bar uses the structured Table reference to show the explicit range. To demonstrate both, I used them both. 

Figure A

excelspillrange-a.jpg

  Use the UNIQUE()When the source data contains repeated values, dynamic function returns a unique listing.

Since the source data is a Table objects, the dynamic functions in H5 will be updated as soon as the Table data is updated. As an example, if you add new records that include a new area, the list starting at H5 will reflect this change. Figure B. If you don’t use a Table to store your source data, and you enter a value that is outside of the original range (in this instance the Region column), you can use the UNIQUE()Function spill range will be Not update. The spillage range is completely eliminated if you remove the dynamic function from H5.

Figure B

excelspillrange-b.jpg

  Because the source data is a Table, the dynamic function updates with the Table’s data.

There has been ample room for the results values so far. What happens if there’s not enough?

What is the #SPILL? error?

Although the previous section has been easy—no errors, no problems—that won’t always be the case. Figure CShows a #SPILL error in the spillage range. E5 has not been changed, the function remains the same. We now see an error instead of the result values that we previously saw.

Figure C

excelspillrange-c.jpg

  Now the dynamic function returns an error.

You’re correct if your guess was that H7 test is in the way. The problem with this error is almost always a lack of cells available to perform the original calculation. If the dynamic function is unable to return all the result values, it returns #SPILL! error.

It’s easy to find the solution. Either delete the infringing data in the spill area or move the original dynamic functions to a cell with enough room to satisfy all the calculated result values. 

If the problem is clear, it’s simple to solve. If the problem isn’t as obvious, it can be difficult to solve. Figure C, don’t despair. Instead, consult the error tag to find out more. When you select an original expression or function, the error tag will be displayed.Figure C). To see the error list, click on the smart tag Figure D.

Figure D

excelspillrange-d.jpg

   The error tag shows the possible errors.

Let’s look at the possible mistakes:

  • Spill Range isn’t BlankIt is most likely the most common error. This is our problem. Figure C. This is evident in the example. But what if you can’t find the problem cell? Select Obstructing Cells is an option to select the cell that’s blocking your ability to find the offending value. You will be taken to the cell that is blocking the results. This will allow you to choose whether to delete or move the dynamic functions.
  • Help with this ErrorOpen the Help files to see a list of error messages.
  • Choose Obstructing CellsIt will choose the cell in the spillage range that is preventing the resulting values. This might indicate merged cell because the spill area isn’t large enough to accommodate them.
  • Display Calculation StepsThe Evaluate Formula dialog allows you to step through the calculations in a similar way as you might do with VBA code in Visual Basic Editor.
  • Don’t Pay Attention to ErrorsThis will allow you to clear the smart tag so that you can continue working regardless of the error. This is not a recommended method unless you have a good reason.
  • Formula Bar EditThis allows you to edit by focusing on the actual expressions or functions in the Formula Bar.
  • Optional Error CheckingYou can view and change the options in the Options dialog.

This is the most common error in our case. The first item is the one we are referring to. Spill Range isn’t BlankIt is clear and informative. If you are unable to locate the offending cell, please choose another option. Choose Obstructing Cells. The other errors will not be repeated often.

The Table object does not support dynamic array expressions and functions. These expressions and functions should not be entered into a Table object. You can and should store the source data in a Table. However, you shouldn’t include the actual function in a Table.

Excel’s dynamic array functions can be used and maintained easily, provided you are familiar with the spill range and how to accommodate it.

To learn more about dynamic array functions, please read the following articles.

Also see