The Spill Range Operator (#) in Excel
By ExcelReady TeamUpdated 5 min read
The short answer
The spill range operator in Excel is the # sign after a cell reference, such as E2#. It means "the whole range that the formula in E2 spilled into". If E2 holds =UNIQUE(B2:B9) and returns four regions, E2# refers to all four cells and grows with the list. It needs Microsoft 365, Excel 2021 or Excel 2024.
Key takeaways
- E2# refers to every cell the dynamic array formula in E2 spilled into, whatever the size.
- Use it inside other formulas, such as =COUNTA(E2#) or =SUMIF(B2:B9,E2#,C2:C9).
- Selecting a whole spilled range while writing a formula makes Excel type the # for you.
- It also works in Data Validation sources and named ranges, which makes lists and charts dynamic.
- A # on a cell that doesn't hold a spilling formula returns #REF!.
On this page+
The spill range operator in Excel is the # sign you put after a cell address, like E2#. It refers to the whole result that a formula in E2 spilled into, so other formulas, drop-downs and charts follow the list as it grows or shrinks. This guide explains how it works, where to use it and how to fix it when it returns an error.
What is a spill range?#
A spill range is the block of cells a dynamic array formula fills with its results. Functions like UNIQUE, SORT, FILTER and SEQUENCE can return many values, so you type the formula in one cell and the results "spill" into the cells below or to the right.
Excel draws a thin blue border around the spill range when you click inside it. Only the first cell holds the formula. The other cells show values but are greyed out in the formula bar.
The examples use this order list in A1:C9, with =UNIQUE(B2:B9) typed in E2:
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Order | Region | Amount | Regions | |
| 2 | 1001 | East | 4200 | East | |
| 3 | 1002 | West | 1850 | West | |
| 4 | 1003 | East | 3100 | South | |
| 5 | 1004 | South | 5600 | North | |
| 6 | 1005 | West | 2400 | ||
| 7 | 1006 | East | 980 | ||
| 8 | 1007 | North | 1500 | ||
| 9 | 1008 | South | 3300 |
The spill range is E2:E5. You can learn more about the function in the UNIQUE function guide.
What does the spill range operator (#) do?#
E2# means "the spill range that starts at E2". Today that is E2:E5. If a new order arrives from "Central" and the UNIQUE range covers it, the spill becomes E2:E6, and every formula that uses E2# includes the new cell with no edits.
That is the problem # solves. A fixed range like E2:E5 misses the fifth region. A whole column like E:E picks up the header and empty cells. E2# is always exactly the right size.
There are two ways to write it:
- Type it. Write the address of the formula cell, then
#:=COUNTA(E2#). - Select it. While writing a formula, drag over the whole spill range E2:E5. Excel writes
E2#for you.
The operator needs Microsoft 365, Excel 2021 or Excel 2024. Older versions don't spill, so there is nothing to refer to.
Examples: use a spill range in other formulas#
Most functions accept a spill range wherever they accept a normal range. With UNIQUE in E2:
| Formula | What it does | Result |
|---|---|---|
=COUNTA(E2#) | Counts the regions | 4 |
=ROWS(E2#) | Counts the rows in the spill | 4 |
=SORT(E2#) | Sorts the list A to Z | East, North, South, West |
=INDEX(E2#,1) | Returns the first item | East |
=SUMIF(B2:B9,E2#,C2:C9) | Totals each region, spilling beside the list | 8280, 4250, 8900, 1500 |
The SUMIF example is the most useful. Put it in F2, and it spills one total next to each region: East 8,280, West 4,250, South 8,900 and North 1,500. Add a region, and both columns grow together. That is a small summary table with no pivot table. The guide to a sorted list of unique values builds this step by step.
If the spill has several columns, INDEX(F2#,,2) returns just its second column. Leave the row argument empty to get every row.
Use spill ranges in drop-downs and named ranges#
The # works outside cell formulas too, and this is where it saves the most time.
- Data Validation. Set a drop-down's source to
=$E$2#, and the list of choices updates when the data changes. See dynamic drop-down lists that update themselves. - Named ranges. In Formulas > Name Manager, create a name such as Regions that refers to
=Sheet1!$E$2#. Then=COUNTA(Regions)reads well and always fits the list. The article on named ranges vs cell references explains when names help. - Charts. A chart can't take
E2#directly in its series box, but it can take a named range that uses it. Point the series at the name and the chart grows with the data. - Other sheets.
=COUNTA(Summary!E2#)works across sheets. Across workbooks, the other file must be open, or you get #REF!.
Why does a spill reference return an error?#
A spill reference fails when the cell before the # isn't the start of a working spill. Check these causes:
- #REF!: no formula in that cell.
E3#fails because E3 is part of the spill, not the formula cell. Always use the first cell, E2. - #REF!: a closed workbook. References to a spill in another workbook only work while that workbook is open.
- The source shows #SPILL!. If something blocks E3:E5, the formula in E2 can't spill and the reference has nothing to return. Clear the blocking cells. The guide to the #SPILL! error covers every cause.
- The formula is in a table. Dynamic array formulas can't spill inside an Excel table, so move the formula to normal cells.
Spill range or a fixed range?#
Use # when the list comes from a dynamic array formula and its size can change. Use a normal range like B2:B9 for ordinary data typed into cells, or turn that data into an Excel table so it grows. The # only works on spills, not on typed lists.
A common pattern is to chain them: =UNIQUE(B2:B9) in E2, totals with E2# beside it, and a sorted copy with =SORT(E2#) somewhere else. One edit to the data flows through all three. If you need to clean the data first, see how to remove duplicates in Excel.
Practise it#
Spill references are a quick way to show an interviewer you know modern Excel. Practise spill references like A2# with the modern Excel functions lesson: you type each formula and it checks it instantly. Then practise UNIQUE in a live spreadsheet to build the lists you refer to. Start with the exercise on this page.
Try it yourself
This is a live spreadsheet. Type the formula in the green cell and press Enter.
E2 holds =UNIQUE(B2:B9), which spills the regions. In H2, count how many regions there are by using COUNTA on the spill range E2#.
| A | B | C | D | E | F | G | H | I | |
|---|---|---|---|---|---|---|---|---|---|
| 1 | Amount | Regions | Region count | ||||||
| 2 | East | ||||||||
| 3 | West | ||||||||
| 4 | South | ||||||||
| 5 | North | ||||||||
| 6 | |||||||||
| 7 | |||||||||
| 8 | |||||||||
| 9 | |||||||||
| 10 | |||||||||
| 11 | |||||||||
| 12 |
Tip: Write the address of the cell with the UNIQUE formula, then add a # straight after it.
Practise this in a lesson
Frequently asked questions
What does the # symbol mean in an Excel formula?
After a cell reference, like E2#, it is the spill range operator. It refers to the full spilled result of the dynamic array formula in that cell, however many rows and columns it has.
How do I reference a spill range in Excel?
Type the address of the cell that holds the formula, then a # sign, for example =SUM(F2#). You can also select the whole spilled range with the mouse while writing a formula, and Excel writes the # for you.
Why does my spill reference return #REF!?
The cell before the # doesn't hold a dynamic array formula, or it points at a workbook that is closed. Make sure you reference the first cell of the spill, the one with the formula.
Can I use a spill range in data validation?
Yes. Put a formula like =SORT(UNIQUE(A2:A50)) in a helper cell, say F2, and set the drop-down source to =$F$2#. The list grows when new values appear.
Which Excel versions support the spill range operator?
Microsoft 365, Excel 2021, Excel 2024 and Excel for the web. Excel 2019 and older don't have dynamic arrays, so they have no spill ranges to refer to.