Spill and dynamic arrays

What spilling is

Normally one formula gives one value in one cell. A formula that returns several values, such as a column of 6 VAT amounts, now writes them out over the neighbouring cells below and to the right.

That is spilling, and the block of cells it fills is the spill range. A result that can change size like this is a dynamic array.

Only the top-left cell, where you typed the formula, contains it. The other cells hold values:

• select one and the formula bar shows the formula in grey; you cannot edit it
• a thin blue border marks the spill range
• delete the top-left formula and the whole spill disappears
• change the source data and the spill grows or shrinks by itself

An array is a set of values that Excel handles as one unit, in rows and columns. A range such as B2:B7 is an array, a typed list such as {1,2,3} is one, and so is the result of FILTER. Spilling is how Excel shows an array that has more than one value.

Which Excel can spill

Spilling arrived with the dynamic arrays of Microsoft 365 and Excel 2021.

Older versions do not spill: a formula for several cells has to be entered as an array formula with Ctrl+Shift+Enter over a block of cells you selected first, and Excel then shows it with {braces} in the formula bar. You cannot type those braces yourself.

Check the version before you send a workbook to a client or a colleague. A formula with FILTER or UNIQUE shows #NAME? in a version that does not have the function, and a spill formula opened in an older version is shown as an array formula over the old size of the spill, which no longer follows the data.

The spill reference operator #

To point at a whole spill, write the address of its top-left cell followed by #. E2# means everything the formula in E2 spills at this moment. Three examples:

• =SUM(E2#) adds it all
• =COUNTA(E2#) counts how many values there are
• in SUMIFS(C2:C9, A2:A9, E2#) the list is a set of criteria

E2# is always exactly as large as the spill, so you never count rows by hand and never miss one.

The operator works only on the cell that holds a spilling formula. Pointed at a cell that does not spill, it gives #REF!.

=SUM(E2#)

=SUM(E2#)

=COUNTA(E2#)

=COUNTA(E2#)

=SUMIFS(C2:C9, A2:A9, E2#)

=SUMIFS(C2:C9; A2:A9; E2#)

=SUM(E2:E4)

=SUM(E2:E4)

What spills

Several functions are built to return many values. A plain formula spills too, whenever it gets a range or an array where it expects a single value: =A2:A10*1.21 gives 9 results, and so does =LEFT(A2:A10, 4). A function that already adds up a range, such as SUM, returns one value and does not spill.

Spilling formulas nest. =SORT(UNIQUE(A2:A9)) lists each value once and puts the list in order; the inner result is an array that the outer function reads in full.

=UNIQUE(A2:A9)

=UNIQUE(A2:A9)

=A2:A10*1.21

=A2:A10*1,21

=XLOOKUP(A2:A9, F2:F20, G2:G20)

=XLOOKUP(A2:A9; F2:F20; G2:G20)

=TEXTSPLIT(B2, "-")

=TEXTSPLIT(B2; "-")

When you get #SPILL!

#SPILL! means the formula is correct but its result has no room. The error appears only in the top-left cell. Select that cell and Excel names the cause in the warning menu. The four causes below are the most common ones.

Do not repair #SPILL! with IFERROR: it hides the message that tells you something is in the way, and the list stays empty.

The @ sign in older files

Before dynamic arrays a formula that got a range where it expected one value did not spill. Excel quietly took one value from the same row or column as the formula. That is implicit intersection: =A2:A10*1.21 typed in D4 meant A4*1.21.

When Microsoft 365 opens such a file it writes @ in front of the part that could return several values, as in =@A2:A10*1.21, so that the formula keeps giving the same single value.

Remove the @ and the formula spills. Do that on purpose and note it: the figures change from one value to a column, and the new spill can run into cells that already hold data.

=@A2:A10

=@A2:A10

Array constants

An array constant is a list of fixed values that you type between braces. With English settings a comma separates the columns and a semicolon the rows.

With Dutch settings a backslash separates the columns and a semicolon still separates the rows, because the comma is the decimal separator. Excel shows the constant in the style of the computer's regional settings.

A constant can hold numbers, text in quotes, TRUE and FALSE and error values, but no cell references and no functions.

Two arrays of different shapes combine into a grid, as {0.005,0.01} does with a column of revenues. Two columns of different length leave #N/A where there is no counterpart.

=B2:B4*{0.005,0.01}

=B2:B4*{0,005\0,01}

=XLOOKUP(C2:C7, {1,2,3}, {0.21,0.09,0})

=XLOOKUP(C2:C7; {1\2\3}; {0,21\0,09\0})

=SUM(COUNTIF(B2:B100, {"MEM","BNK"}))

=SUM(COUNTIF(B2:B100; {"MEM"\"BNK"}))

Related topics

• Cell references: relative, absolute and mixedA cell reference is the address a formula reads. Dollar signs decide whether it moves when you copy the formula, and getting that right is what lets one formula fill a whole column or table.
• Error valuesAn error value is a result that starts with # and names what went wrong, such as #N/A or #DIV/0!. It spreads through formulas, and hiding it can hide a gap in your audit evidence.
• OperatorsOperators are the symbols in a formula that calculate, compare, join text or combine references. Excel applies them in a fixed order, which explains many results that look wrong.