Concept
Availability: Microsoft 365, Excel 2021/2024, Excel for the web. (Google Sheets has similar functions.) Older Excel shows these formulas as {=...} array formulas or #NAME?.
1. One formula, many results
Practice data: tblSales (16 rows). In H2 type:
=UNIQUE(tblSales[Region])
Press Enter — North, South, West appear in H2:H4. Only H2 contains the formula; H3:H4 are the spill range (blue border when selected, formula greyed in the formula bar).
Even simple maths spills: =tblSales[Qty]*2 returns 16 results.
2. The # operator — refer to the whole spill
In I2:
=SUMIFS(tblSales[Amount], tblSales[Region], H2#)
H2# means "whatever H2 spilled", now 3 cells. Result: 5,09,000 · 2,59,000 · 3,02,000. When a new region appears in the data, H2# grows and I2 grows with it — no copying formulas down.
Use H2# in charts (via a named range), data validation lists (=$H$2#), conditional formatting and other formulas.
3. #SPILL! — the result has no room
If any cell in the spill area isn't empty, you get #SPILL!. Click the warning icon → "Select Obstructing Cells", clear them, and it spills.
Other causes:
- Spilling inside an Excel Table (Tables can't hold spilled arrays — put dynamic formulas outside Tables).
- Result too big for the sheet edge.
- Merged cells in the way.
4. Implicit intersection and @
Old Excel silently reduced a range to one value in some formulas. New Excel shows that behaviour with @:
=@tblSales[Amount]
means "the single value from the same row". If you open an old file and see @ added, it's preserving old behaviour — you can usually leave it.
5. Arrays in calculations
=SUM((tblSales[Amount] >= 100000) * tblSales[Amount])
tblSales[Amount] >= 100000 makes 16 TRUE/FALSE values; × Amount keeps only the big orders → 4,93,000 (4 orders). Comparisons and maths on whole columns return arrays inside the formula — no Ctrl + Shift + Enter needed any more.
6. Good habits
- Leave empty space below/right of dynamic formulas.
- Put a header above each spill and reference it with
#. - Don't type values inside a spill area.
Common mistakes
Something typed in the spill area (#SPILL!). Writing a dynamic formula inside a Table. Referencing H2:H4 instead of H2# (breaks when the list grows).