Skip to content
Tutorials Center
My learning

Topics

Lesson 46 of 93 ยท Dynamic Arrays

Dynamic Arrays and Spilling

Understand Excel dynamic arrays: how a formula spills into many cells, the # spill operator, #SPILL! causes, implicit intersection, legacy CSE and versions.

For most of Excel's history, a formula returned one value into one cell. If you wanted a formula to produce a list, you had to select the right number of cells in advance and press Ctrl+Shift+Enter. Since the Microsoft 365 release of dynamic arrays, a formula can return a whole array of values, and Excel automatically writes them into as many neighbouring cells as needed. This is called spilling, and the filled area is the spill range.

This is the biggest change to Excel formulas in decades, and it explains many newer functions (FILTER, SORT, UNIQUE, SEQUENCE) as well as the odd symbols you see in modern files: #, @ and the #SPILL! error. The formulas are real Excel. The runnable examples use a stand-in spill engine written in JavaScript, which follows the same rules on a tiny grid so you can see what lands where.

One formula, many cells

Anchor The formula lives in the top-left cell.
Spill Results flow right and down by themselves.
Blue border Click a spilled cell to see the range.

Only the anchor cell holds the formula. The rest are read-only results.

1 / 2

Your first spill

Type numbers in B2:B5, and in D2 enter:

TEXT
=B2:B5 * 2

In Microsoft 365 you press Enter, and four results appear in D2:D5. Click D3 and you will see a pale formula =B2:B5*2 in the formula bar, and a thin blue border around D2:D5. You cannot edit D3 on its own: it is part of the array. To change the result, edit D2. To remove it, delete D2 and the whole spill disappears.

A spill can be two-dimensional. =SEQUENCE(3, 4) fills a 3ร—4 block. Functions that return a whole table, such as SORT and FILTER, spill in both directions. And any ordinary formula that does arithmetic on a range now spills: =A2:A9 & "!", =B2:B9 > 50, =ROUND(B2:B9, 1).

JS A stand-in spill engine, with #SPILL!

Press Run (or Ctrl + Enter) to see the console output here.

No output.

In step 2 only D2 is left, with #SPILL!; the results are not written at all rather than partly overwriting your data. As soon as the path is clear, the spill appears again by itself.

The spill range operator #

To refer to a whole spill range, put # after the address of its top-left cell. If D2 spills down to D5, then D2# means D2:D5, and it automatically grows or shrinks when the spill does. This is far better than a fixed reference like D2:D5.

TEXT
=SUM(D2#)             total of the spilled values
=COUNTA(D2#)          how many results
=ROWS(D2#)            height of the spill
=SORT(D2#, , -1)      sort someone else's spill

# works in other places too: as a chart's data source, in a conditional formatting rule and in a dropdown list rule (see Data Validation and Dropdown Lists). If the cell is not the anchor of a spill, D2# gives #REF!.

Why #SPILL! happens

#SPILL! means Excel knows what the formula returns but cannot place it. The usual causes:

  • Something is in the way: a value, a formula, or even a space in a cell of the target area. Click the error icon and choose Select Obstructing Cells. Cells that merely look empty can hold a formula returning "".
  • Merged cells in the area. Unmerge them.
  • An Excel Table: spilled formulas cannot live inside a table. Put the formula outside the table, or use a regular range (see Excel Tables).
  • The result would be too large and run off the sheet, for instance a whole-column reference in a function that returns one value per cell.
  • The size is unknown or unstable when Excel recalculates, such as some volatile constructions.
  • Out of memory. Rare, but possible with huge arrays.

See also Formula Errors and How to Fix Them.

Implicit intersection and @

Before dynamic arrays, if a formula gave a range where a single value was expected, Excel quietly took the one value that sat in the same row (or column) as the formula. This hidden behaviour is implicit intersection. For example, =B2:B5 typed into D4 simply returned B4.

Now that formulas can return arrays, Excel makes the rule visible with the @ operator:

TEXT
=@B2:B5        the value of B2:B5 in this formula's row
=@INDEX(...)   force a single value from a function that may return many

When you open an old workbook, Excel adds @ to old formulas where the behaviour could differ, so they keep calculating as before. You can usually delete the @ if you want the formula to spill. If the row or column of the formula is outside the range, the intersection fails and you see #VALUE!.

Legacy array formulas (CSE)

In older Excel, array formulas were entered with Ctrl+Shift+Enter, which wrapped them in braces: {=SUM(B2:B5*C2:C5)}. Two sorts exist:

  • A single-cell array formula computes with arrays inside but returns one value. These still work, and SUMPRODUCT was often used to avoid the key combination (see SUMPRODUCT).
  • A multi-cell array formula was entered into a selected block of fixed size. It cannot spill. If the block is too small, results are cut off. If it is too big, extra cells show #N/A. You cannot change part of the block.

Dynamic array formulas replace both: no special keystroke, and the size follows the data. In Microsoft 365, legacy formulas keep their braces and still work; you can convert one by editing it and pressing Enter.

JS @ intersection, # references and legacy fixed arrays

Press Run (or Ctrl + Enter) to see the console output here.

No output.

Which versions have dynamic arrays?

Dynamic arrays are available in Microsoft 365 (Windows, Mac, web and mobile), Excel 2021, Excel 2024 and Excel for the web. They are not in Excel 2019, 2016 or earlier. Watch out when sharing files:

  • Opening a workbook that uses FILTER or UNIQUE in Excel 2019 shows #NAME? (the functions are stored with an _xlfn. prefix) and spilled results become static copies of the last values.
  • Dynamic array formulas open in old versions as legacy {=...} array formulas of a fixed size.
  • If your audience may have old Excel, use SUMIFS, INDEX/MATCH and helper columns instead, or send a PDF or values-only copy.

Check File โ†’ Account โ†’ About Excel if you are unsure what you have. The next lesson puts spilling to work: FILTER, SORT, SORTBY and UNIQUE.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. You enter =B2:B5*2 in D2 in Microsoft 365. What happens?

    Dynamic array formulas return the whole array and spill it into the cells below automatically.
  2. 2. What does D2# refer to?

    The # operator means "the whole spill range that starts in this cell", and it resizes automatically.
  3. 3. Which of these is a common cause of #SPILL!?

    Excel will not overwrite existing contents, so anything in the target area blocks the spill. Use "Select Obstructing Cells" to find it.
  4. 4. What does =@B2:B6 return when typed in row 4?

    The @ operator performs implicit intersection and picks the value on the formula's own row.
  5. 5. Which statement about legacy Ctrl+Shift+Enter array formulas is true?

    A legacy multi-cell array is tied to the block you selected. Dynamic arrays removed that limit.

Summary

  • A dynamic array formula returns many values and spills them into neighbouring cells from its anchor cell.
  • A2# refers to the whole spill range and grows with it; use it in other formulas, charts and dropdowns.
  • #SPILL! means something blocks the area: contents, merged cells, a table, or an oversized result.
  • @ makes implicit intersection explicit and gives a single value; old workbooks may show @ in converted formulas.
  • Legacy {=...} CSE arrays have a fixed size; dynamic arrays size themselves.
  • Dynamic arrays need Microsoft 365, Excel 2021/2024 or Excel for the web, not Excel 2019 or older.

Next: FILTER, SORT, SORTBY and UNIQUE.