Excel spill range explained
by Svetlana Cheusheva, updated on
The tutorial explains the idea of "spill range" in simple language and gives answers to the most common questions.
Spilling is one of the new features introduced in Excel 365 as part of the dynamic arrays functionality. And to make the most of it, it's important to understand the key terms, so you can follow examples and efficiently build your own dynamic array formulas. Understanding the spill range concept is the focus of this tutorial.
Spill range in Excel
When a dynamic array formula produces multiple values as the result of calculation, it outputs or spills all those values onto the sheet.
Spill range refers to an array of values returned by a dynamic array formula in neighboring cells.
When you select any cell in the spilled area, the entire range is highlighted with a blue border indicating that everything inside it is calculated by the formula in the topmost cell.
Previously, with traditional CSE array formulas, we had to guess how many cells to copy a formula to. Now, you just enter the formula in a single cell and let Excel take care of the rest.
The spill range is dynamic and updates automatically as the source data changes. When you add or remove items to/from the source data, the spilled range may expand or contract.
If you delete the formula in the first cell, all the results will be gone.
Spill range reference (# operator)
To refer to a whole spill range, put a hash tag (#) after the address of the upper left cell in the range (i.e. the cell containing the formula that returns an array of values). In terms of Excel, this is called a spill range reference.
For example, we have a UNIQUE formula in C2, which returns multiple results. To reference all those values, type:
=C2#
To refer to the same spill range from another sheet, include the sheet's name followed by the exclamation point:
=Sheet1!C2#
To reference the spill range from another workbook, also include the file's name enclosed in square brackets:
=[Book1.xlsx]Sheet1!$C2#
Referring to C2# is the same as referencing the entire range (=C2:C5). But unlike a regular range reference, the spill ref automatically reflects changes in the range size, so you don't need to manually update all formula references as your source data changes. This behavior is especially useful when you are "feeding" the spill range to another function, either dynamic or conventional one. You can even use it for named ranges and data validation, e.g. to make a dynamic dependent dropdown list.
In our case, to find how many unique names are returned by the dynamic array formula in C2, supply the spill range reference to the COUNTA function:
=COUNTA(C2#)
Tip. To quickly refer to a spill range, select all the cells inside the blue box using the mouse. Excel will create an appropriate spill reference automatically.
Note. The spilled range operator only supports references to open workbooks. If the source workbook is closed, a #REF! error will occur. To fix the error, just open the referred workbook.
3 things you should know about Excel spill range
The spill range is a really wonderful feature that makes the lives of Excel users a lot easier. Below are a few interesting facts that lend some insight into the concept.
Only top-left cell can be edited
As only the topmost cell in the spilt area contains the formula, only that cell is editable. When you select any other cell within the blue border area, the formula is still displayed in the formula bar but is greyed out.
When you change the formula in the first cell and press Enter, Excel automatically updates all other values in the spilled area.
What is and what is not included in spill range
As mentioned above, the spilling behavior is dynamic - when the original data changes, the spilled area adjusts accordingly. For example, if you change the original list so that it contains one more unique name (Carter), the spill range automatically expands to include that name.
However, the spill range does not update when new entries are added outside the referred range.
For example, if the formula refers to A2:A10, and a new item is entered in A11, it won't appear in the results:
For the new item to be included in the formula results, you need to change the referred range to A2:A11.
If you want such changes to be reflected on the fly, then put your source data into an Excel table and use structured references in your formulas. Unlike ranges, Excel tables expand automatically to incorporate new rows. The same effect can be achieved with a dynamic named range.
#SPILL error
If something is blocking the spill range (e.g. other data, spaces, non-printing characters, formulas in the below cells, etc.), a #SPILL error occurs. To resolve the error, clear the obstructing cells. For more information, please see SPILL error in Excel - causes and fixes.
To wind things up, we are posting answers to the three frequently asked questions (3 seems to be a magical number in this tutorial :)
How do you clear spill range in Excel?
Depending on what you are trying to achieve, there are two solutions:
To resolve a #SPILL error that occurs because spill range isn't blank, either remove the blocking data from the spilt area or move the formula to a new location where there are enough empty cells to output all the results. Please follow the above link for more details.
To remove all values in the spilt area, delete the formula in the first cell.
How do you change spill range in Excel?
As the spill range is the result of a formula, it cannot be changed manually.
In case you need to modify or update the formula, select the upper left cell in the spilled area, make the required changes and press the Enter key.
How do you fix a spill range if it's too big?
The size of a spill range is determined by Excel and changes automatically as the source data or the formula gets updated.
In some situations, you can set the spill range size from within the formula itself. As an example, please see How to limit the number of rows returned by FILTER.
If you are faced with a #SPILL error, please check out these solutions: How to fix a #SPILL! error in Excel.
How do you get rid of spill range in Excel?
Again, the answer depends on your ultimate goal.
If you are looking to disable the spilling feature globally, it's not possible - there is no such setting in Excel. In fact, it's one of the most useful new capabilities and it makes no sense to refuse it altogether. It would be wiser to learn how to use it to your advantage :)
To remove a particular spill range, delete a formula in the first cell.
To prevent a formula from spilling into multiple cells, use the @ operator which reduces multiple values to a single value. In terms of Excel, this is called implicit intersection.
For example, the dynamic array formula below multiplies each value in A2:A5 by 10%. The result is a spill range that occupies 4 cells.
= A2:A5*10%
To process just one value (in the same row as the formula) and return the result in a single cell, change the formula as follows, and then copy it to as many cells as needed.
=@A:A*10%
or
=A2*10%
Now, you are no longer a novice as far as Excel spill range is concerned, right? I thank you for reading and hope to see you on our blog next week!
You may also be interested in:
Table of contents
