Module 1: Dynamic Array Formulas
How Spill Ranges Work
For years, an Excel formula returned one value into one cell. If you wanted a list of results, you copied the formula down and hoped nobody inserted a row in the wrong place. Microsoft 365 Excel changed this with dynamic arrays: a single formula can now return many values, and Excel places them in neighbouring cells automatically. This behaviour is called spilling. Understanding it is the foundation for everything else in this module, because FILTER, SORT, UNIQUE, SEQUENCE and the newer functions all depend on it.
What a spill range is
Type this into an empty cell, say D2, in a sheet where A2:A10 holds customer names:
=SORT(A2:A10)Excel returns nine sorted names, filling D2:D10. Only D2 contains the formula. The cells below show the results with a faint blue border when you select any of them, and the formula appears greyed out in the formula bar for those cells. The whole block is the spill range, and D2 is its anchor.
The spill range resizes itself. If you add names to A2:A10 the result updates; if the source were a formatted Excel Table, the formula would also pick up new rows automatically. That is why dynamic arrays and Tables work so well together.
Referring to a spill range with #
Because the size of a spill range changes, you need a way to refer to all of it. Add the spill operator # after the anchor cell:
=COUNTA(D2#)
=D2#&" (customer)"
=XLOOKUP("Wanjiku",D2#,D2#,"Not listed")D2# always means "the whole current spill range starting at D2", whether it holds nine rows or nine hundred. This is far more reliable than typing D2:D10, which breaks when the list grows.
Array arithmetic spills too
You do not need a special function to spill. Any calculation on a range returns an array. If B2:B10 holds unit prices and C2:C10 holds quantities:
=B2:B10*C2:C10returns nine line totals in one formula. To get the grand total directly:
=SUM(B2:B10*C2:C10)In older Excel this needed Ctrl+Shift+Enter. In Microsoft 365 you just press Enter.
The #SPILL! error and its causes
If Excel cannot place the results, the anchor shows #SPILL!. Click the warning icon to see the reason. The usual causes are:
| Cause | Fix |
|---|---|
| Something is already in the cells the result needs, even a space | Clear the blocking cells; use Select Obstructing Cells from the warning menu |
| The formula is inside an Excel Table | Tables do not allow spilling; put the formula outside the Table |
| The spill range includes merged cells | Unmerge the cells |
| The result is too large for the sheet, for example a whole column reference that spills past the last row | Refer to the exact range or a Table column instead of whole columns |
Implicit intersection and the @ sign
Older workbooks expected formulas to return one value. When you open such a workbook in Microsoft 365, Excel may show an @ in some formulas, such as =@A2:A10. The @ means "return a single value, the one on the same row", which is how the formula used to behave. Leave it in place in legacy files. When writing new formulas, you rarely need it.
Worked example: a live list of branches
You keep a Table named Sales with columns Date, Branch, Rep, Product, Units and Amount. On a report sheet you enter in A2:
=SORT(UNIQUE(Sales[Branch]))It spills the branch names, perhaps Eldoret, Kisumu, Mombasa, Nairobi. In B2 you enter:
=SUMIFS(Sales[Amount],Sales[Branch],A2#)Because the criteria argument is the spill range A2#, SUMIFS returns one total per branch, spilling down beside the names. When a new branch, say Nakuru, appears in the data, both columns grow by one row with no editing.
Common mistakes to avoid
- Typing values or notes below a spill anchor, which later blocks the spill.
- Referring to
D2:D10instead ofD2#, so new results are ignored. - Putting dynamic array formulas inside an Excel Table.
- Using whole column references like
A:Ain heavy array formulas, which slows the workbook and can cause spill errors.
Exercise
Create a Table named Sales with at least 30 rows and columns Date, Branch, Rep, Product, Units and Amount. On a new sheet, spill a sorted unique list of reps in A2 and use SUMIFS with A2# to spill each rep's total in B2. Add a new rep to the Table and confirm both columns grow. Then deliberately type something below A2 to see #SPILL! and fix it.
Key points
- A dynamic array formula lives in one anchor cell and spills its results into neighbouring cells.
- Use the spill operator, as in D2#, to refer to the whole current spill range.
- #SPILL! usually means blocked cells, a formula inside a Table, merged cells or an oversized result.
- Feeding a spill range into another function, such as SUMIFS criteria, returns one result per item.