Why does an old formula now give #SPILL!?

In Excel 365, a formula that reads a whole column (=A:A*2) now returns a whole column of results. Write =A2*2 and copy it down, or limit the range.

The #SPILL! error

Since Excel 365, a formula can return a whole array, which spills into the neighbouring cells. If one of them is taken, the formula returns #SPILL! instead of overwriting anything.

What it means

#SPILL! means the area the result should spill into is not free. Clicking the cell shows the intended area with a dotted border.

Where it comes from

Cells not empty in the area: a value, a formula, or a single space.
Merged cells in the area.
The formula is inside a structured table, where spilled results are not allowed.
The result exceeds the sheet: =SEQUENCE(2000000) or a whole column passed to a formula that copies it.
A size unknown in advance, with a volatile function such as RANDBETWEEN inside SEQUENCE.

Fixing it

Clear the cells in the dotted area.
Unmerge the cells: Unmerge cells does it across the workbook.
Convert the table to a range (Table Design, Convert to Range), or move the formula out of the table.
If a single value is wanted, prefix with @: =@A:A takes the value from the same row.

Tools that help

Unmerge cells: removes merges and copies the values.
Explain a formula: flags whole columns in array calculations.

Frequently asked questions

Why does an old formula now give #SPILL!?: In Excel 365, a formula that reads a whole column (=A:A*2) now returns a whole column of results. Write =A2*2 and copy it down, or limit the range.