When a formula spills, the # operator lets you refer to the whole result — however big it grows — with one reference. Point a chart, a drop-down, or another formula at A2# and it tracks the spill automatically.
A2# means “the whole spill range anchored at A2.” It resizes with the spill, so dependent formulas never break.
The example
A2 holds a spilling list; A2# refers to all of it at once.
| A | C | D | ||
|---|---|---|---|---|
| 1 | =SORT(...) | Formula | Result | |
| 2 | East | =COUNTA(A2#) | 4 | |
| 3 | North | =SUM(B2#) | ||
| 4 | South | |||
| 5 | West |
The formula
Count everything the spill produced, however long:
How it works
The # turns a single anchor into the full range:
- A dynamic-array formula in
A2spills down (and/or across) into a range. - Writing
A2#— the anchor cell plus#— references that entire spilled range, whatever its current size. - If the spill grows from 4 items to 40,
A2#automatically covers all 40 — no editing the range. - Use it anywhere a range goes:
SUM(A2#), a chart source, or a Data Validation list (=$A$2#) for a self-sizing drop-down.
Self-updating drop-down: put =SORT(UNIQUE(range)) in A2, then set Data Validation → List → Source to =$A$2#. The drop-down grows and shrinks with the data — no fixed range to maintain.
Try it: interactive demo
Change how many items the formula in A2 spills; A2# tracks it.
itemsVariations
Sum or count a spill
Aggregate the whole output:
Self-sizing drop-down
Data Validation list source:
Chain off a spill
Filter the spilled result further:
Pitfalls & errors
#REF! if the anchor isn’t a spill. A2# only works when A2 actually spills. On a normal single-value cell it errors.
The # follows the anchor. Reference the top-left cell of the spill (where the formula lives), not a cell in the middle of the output.
Excel 365/2021 only. The spill operator doesn’t exist in Excel 2019 and earlier.
Practice workbook
Frequently asked questions
What does the # symbol do in Excel formulas?
How do I make a drop-down that grows automatically?
Why does my A2# reference return #REF!?
Stop fighting formulas. Learn them in a day.
This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.
See the Formulas & Functions Class