by 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!
33 comments
I am trying to use the # reference to make a formula spill down in line with the array I have created using the UNIQUE function.
=IF($J12#>0,$N12,0)
J12 is where the first row of the array starts and I want my If formula to Spill down in line with wherever the array in J12 extends to.
However my IF formula currently spills down successful, but only ever references N12. I would like the $N12 reference to automatically adjust like a traditional excel formula would if I dragged it down, i.e $N13, $N14 etc.
Is this possible?
I'm trying to use the spill function to not have as many redundant formulas being dragged down to cater for multiple array sizes in J12.
Hi! Unfortunately, your formula is written incorrectly, and I can't understand what you wanted to do. The reference $N12 should change to $N13 and so on when you copy the formula down the column. Read more: How to copy formula in Excel with or without changing references.
I am struggling with the same thing as James, for me it's pretty clear what he wants to do.
I also want to drag down the dynamic array formula and then vstack the outcomes of it in separate cells. don't know how long the dynamic arrays will be, and if I have more than a few of them, the process of manually adjusting the position of source data to adjust to dynamic array lenght is absurd.
It will be much easier if excel added/deleted automatically extra rows generated by dynamic array.
Hi! Try using a Excel table instead of a dynamic array. Maybe this article will be helpful: Structured references in Excel tables.
Not being able to turn off a function you are not coding makes absolutely no sense! The spill function makes me NOT want to use excel! Why can Microsoft not make this a selection! A simple switch to use or not use?
I agree with Heather. The spill function blocks formulas that have worked previously. I am currently trying to round a column of numbers into 1 total, and it is trying to give me 10 amounts for the 14 cell range. This is Unnecessarily complicated for the basic spreadsheets I usually need.
Hello,
Is it possible to look up a value in spill range and return corresponding value from another spill range?
I have 2 sheets:
On sheet 1 all the columns have spill range
Column A | Column B | Column C
(=unique names) (spill range) (spill range)
On Sheet 2:
Column D | Column E
(excel table, list of names from
Column A + more names)
What I want to achieve is: In Column E I want to look up the name from Column D and compare with names in Column A -> IF the name is in both, return the corresponding value from Column C
If not, return 0.
I tried to use the IF function and xlookup function, using the # to reference spill range. But every time I am able to get only the value for the 1st name on the list (Column D) All others is coming with an error.
Is there a way to do it?
The @ operator does not work in my excel formula:
=@B2:B9/8
I get this result:
#VALUE!
The Help section says that the formula should be:
=[@Column1]
However, when I try using brackets in a formula, I get this error message:
"The syntax of this name isn't correct."
If I use parentheses (), then I still get the #VALUE! error.
Is there a reason that the @ symbol may not be working for me?
Hi!
Write your formula in row 2. Read more about using @ operator here: Excel implicit intersection and @ operator.
Hi, Is there a way to spill into columns rather than rows?
Yo can do this by adding TRANSPOSE function in front of your existing fuction
From within the Spill is there an easy way to return to the source row for editing?
The requirement is to identify rows based on a criteria and then return to the source to change the criteria given once it has changed thereby removing it from the spill filtered data.
I guess there may be a way to stop formula from spilling. Try using the old technique pressing ctrl + shift + enter after you've written the formula - this way the formula won't spill (hopefully).
Hi Muhammad,
Nope, Ctrl + Shift + Enter won't prevent a formula from spilling, but the @ operator will. It is called the implicit intersection operator, and you can learn how it works in the above-linked tutorial.
Hi everyone,
I need to use the spill in a named range / table like environment (not actual table) where I can sort/filter the value generated from spilled formula. However, every time I tried to sort the spill column the excel file get error and I am forced to reload (I'm using the web version), and when returns the formula get jumbled (spill formula does not refer to the correct cell). Any thought on this?
Thanks in advance
This worked! Thank you so much.
Is it possible to dynamically reference values from the spill range when the user selects a row?
In your example showing only the top row can be edited, the value "Ava" is selected. Is there a way to determine that value has been selected and use that value as a dynamic lookup in another formula?
I'm not looking for the data validation drop down list, but to use a value from a cell in the selected row in a second filter() function,
Hello!
You can use cell reference C3 in another formula. But no Excel formula can determine in which cell the cursor is located.
OK so basic question. I have 10 rows of numbers; I want to add rows 2-9 and subtract them from row 1. I only want 1 answer. I used to enter the equation =SUM(A1)-A2:A10 and received 1 answer. Now I either get a #SPILL error or depending on where parenthesis are entered =SUM A1-(A2:A10); I get numbers in 8 rows. I can overcome by doing the addition in one cell and then go to another cell and do the subtraction.
Hi!
To solve your problem, use the formula
=A1-SUM(A2:A10)
Thank You very much
How can I override a spill result in the idle of an array? Meaning, I want to hard-code one or more cells but the #Spill! Error generates.
I had to bury the '@' prefix in a 'VALUE' function because Excel 365 insisted on "helping" by changing a leading '@' to '='.
=VALUE(@CELL("width",A10))
Stupid is as stupid does.
M$ should put themselves out of our misery.
"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 :)"
IOW: Resistance is futile -- you have been assimilated.
Why not make that @ switch the Enabler for spilling? I very much agree with the other posters that losing control is bad news, and the spilling interferes with regular excel usage. That above example for the @ switch is pretty poor, you would not refer to the entire range if you want to do the line-by-line 10% multiplication, and maybe some of them need a different value, as I often use. The examples (and similarly the ones from MS) treat Excel like a database, which shows poor form. I can only hope that the upcoming Office 2021 does an 8.1, and regresses back to useful functions, removing all kinds of hidden automation. (ppt designer, multi-author editing, autosave, all horrid)
Hi Max,
The "@ example" implies that you want to multiply an entire column by the same number. To do a line-by-line multiplication, use a traditional single-cell formula like =A2*10%, and then drag it down to as many cells as needed.
"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 :)"
You are either not a power user (who wants to maintain full control of what's produced), or a fangirl.
It makes UTTER sense to want spill OFF altogether.
1. For starters, when mocking up various models, you do not want to launch into a massive complete table that is applied globally. You want to grab a few variables and test for a few lines of code. You do NOT want to spill at this stage.
2. When you are looking for complete control and want to manage the length of your tables using conditional formulas or conditional formatting.. which you may want to do for data tables use in graphs, or simply to keep it at a summary / dashboard view level.
3.. I can go on and on.. but having any default behaviour that prevents direct full control over the outcome, is RUBBISH. A new feature, and one which autoruns itself, should be capable of being switched off.
Hello,
I can think of one reason (surely there are more) why Microsoft didn’t provide a global switch-off setting – shared workbooks. Imagine that someone has shared a file containing spilled formulas with you, and spilling is off in your Excel. At the very best, you would get one result instead of many, at worst – a bunch of errors.
Anyway, spilling is a reality already. As with any new feature, there are pros and cons, and a number of unhappy users. I just don’t see any point in complaining. To stay productive, you'd better learn how to live with it and leverage its strengths. Want full control? No problem – depending on your context, refer to individual cells rather than ranges or use the implicit intersection operator, and you’ll have the traditional one-formula-one-cell behavior.
The only real problem I see is legacy workbooks. Some formulas that worked fine in older versions may return #SPILL errors in new Excel. As Brendan said below, this can take thousands of man hours to correct. Unfortunately, we ran into this too :( And indeed, it took many hours to fix each formula manually.
OMG agreed, this function is rubbish
How much crack was this idiot smoking when they decided to make it so you can't disable this feature? It's making it so we can't edit/use a ton of existing spreadsheets which have been reliable for decades. This will take thousands of man hours to correct. Do you guys have any pull with Microsoft that you could convince them this was a bad idea?
"If you are looking to disable the spilling feature, it's not possible"????????
Most idiotic thing I have ever seen! Thank you.
Now my company uses Open office, because we can not throw away our 22 year work. An to remake all our work to work with your "new feature" will take next 22 years (if possible at all).
So again. Thank you, you saved us lot of money.
The question really is, why do business keep forking money over to Bill Gates when there are free alternatives to MS Windows (Linux) and to MS Office (LibreOffice, Open Office, etc). There's no need in this day and age to pay for office software.
Hello Margus,
For starters, spilling is Microsoft's new feature, not ours :)
It sounds like you are very frustrated with your experience in new Dynamic Array Excel. Upon a closer look, however, you may find out that it is not as bad as you think and it and does have a handful of useful features and capabilities.
Though spilling cannot be disabled globally in Excel 365, you can "turn off" the spill behavior for any given formula. For this, simply use the implicit intersection operator.