9.1 Spill Ranges and the # Operator
A dynamic-array formula returns many values; they spill into neighbouring cells. Only the top-left cell contains the formula; the rest show it greyed in the formula bar.
Steps in Excel
- In K2 type
=UNIQUE(tblMini[City])› Enter. Six cities spill into K2:K7 with a blue border. - Refer to the whole spill with #:
=COUNTA(K2#)→ 6.K2#grows/shrinks automatically. - Type anything in K5 – K2 shows #SPILL!. Click the ⚠ › Select Obstructing Cells, clear them, and the spill returns.
- Spills don't work inside an Excel Table – put dynamic-array formulas outside Tables.
- Old array formulas (Ctrl + Shift + Enter, shown with
{ }) are no longer needed in Microsoft 365.
Implicit intersection @: if you open a Microsoft 365 formula in older Excel, or if Excel must return a single value, you may see @ (e.g. =@A2:A10), meaning "one value from this range".
Ravindra Bagale's Tip
When they see #SPILL!, many students think the formula is wrong. The formula is correct – there just isn't room below/to the right (or it was written inside a Table). Keep the spill area empty, and to refer to a spill use K2# – if you hard-code K2:K7, it breaks when a new city is added.
Ravindra Bagale's Tip – मराठी
#SPILL! दिसला की बरेच students formula चुकला असं समजतात. Formula बरोबर असतो – फक्त खाली/उजवीकडे जागा नसते (किंवा तो Table च्या आत लिहिला आहे). Spill area रिकामा ठेवा, आणि spill ला refer करायचं असेल तर K2# वापरा – K2:K7 hard-code केलं तर नवीन city आली की चुकतं.
Ravindra Bagale's Tip – हिंदी
#SPILL! दिखते ही बहुत से students समझते हैं formula गलत है. Formula सही होता है – बस नीचे/दाईं तरफ़ जगह नहीं होती (या वह Table के अंदर लिखा गया है). Spill area खाली रखो, और spill को refer करना हो तो K2# इस्तेमाल करो – K2:K7 hard-code किया तो नई city आते ही गलत हो जाएगा.
Practice task
Spill the unique list of categories, count it with #, deliberately block it to create #SPILL!, and fix it.