Ravindra BagaleCourses & study guides

9. Dynamic Arrays

9.1 Spill Ranges and the # Operator

By Last updated:

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

  1. In K2 type =UNIQUE(tblMini[City]) › Enter. Six cities spill into K2:K7 with a blue border.
  2. Refer to the whole spill with #: =COUNTA(K2#) → 6. K2# grows/shrinks automatically.
  3. Type anything in K5 – K2 shows #SPILL!. Click the ⚠ › Select Obstructing Cells, clear them, and the spill returns.
  4. Spills don't work inside an Excel Table – put dynamic-array formulas outside Tables.
  5. 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

#SPILL! disla ki khup students formula chukla samajtat. Formula barobar asto – fakt khali/ujvikade jaga nasate (kiwa to Table chya aat lihila aahe). Spill area rikama theva, aani spill la refer karaycha asel tar K2# vapra – K2:K7 hard-code kela tar navin city aali ki chukta.

Practice task

Spill the unique list of categories, count it with #, deliberately block it to create #SPILL!, and fix it.