Google Sheets: Array result was not expanded because it would overwrite data

Hi, it's BlueByte. When Google Sheets shows #REF! with the note Array result was not expanded because it would overwrite data in [cell], your formula is fine — it produced a block of results, but something already sits in the cells that block needs, so Sheets refuses to paint over your data. A close cousin is an ARRAYFORMULA that returns only one row instead of filling the column. We'll walk through both, why they happen, how to confirm which one you've got, fix each with the output to expect, and keep the spill working.

What "Array result was not expanded" means

Some formulas return more than one value — FILTER, SORT, UNIQUE, IMPORTRANGE, or anything wrapped in ARRAYFORMULA. Sheets tries to spill that block starting from the cell you typed it in, filling the neighbouring cells to the right and below. If any target cell already holds a value it can't, because silently overwriting your data would be worse than erroring:

#REF!
Array result was not expanded because it would overwrite data in B5.

The cell it names (B5 here) is the first occupied cell in the way. This is not a broken formula — it ran, produced a result, and had nowhere to put it.

The two symptoms people confuse

Two distinct problems look related:

• A blocked spill — the #REF! above. The formula wants several cells and one is taken.
• A formula that only fills one row — you wrote =A2:A*B2:B expecting a whole column of products, but only row 2 calculates. The formula isn't array-aware, so it never tried to spill.

They have opposite causes: the first spilled and was blocked; the second never spilled at all. Tell them apart before you reach for a fix.

Why an array result gets blocked or stays put

The blocked-#REF! case comes from one of:

• Data below or beside the formula — a value typed into the spill range, often a stray entry several rows down.
• Another formula's output landing in the same cells.
• A merged cell inside the target range — merged cells can't receive a spilled value.

The one-row case has a different root: a plain arithmetic or lookup formula over ranges that Sheets won't auto-expand, so it needs an explicit ARRAYFORMULA wrapper — or the two ranges you're combining aren't the same size.

Confirm what's in the way and whether the formula is array-aware

Don't guess which cell blocks it — the message names one, but there may be more. Count the non-empty cells below the formula in the column the result should fill:

=COUNTA(B2:B1000)

If that's higher than the header rows you expect, something is parked in the spill range. For the one-row problem, check whether the formula is wrapped: an ARRAYFORMULA shows its function prefix in the formula bar. Google's docs note that many array formulas expand into neighbouring cells automatically, and that pressing Ctrl+Shift+Enter while editing adds the ARRAYFORMULA( prefix for you.

Clear the cells the result needs (the #REF! fix)

Once you know what's blocking the spill, empty those cells — delete the stray values, or move the array formula to a clear column:

=FILTER(A2:A, A2:A<>"")

With the target column empty below the formula, the same FILTER that returned #REF! now spills the whole result. If a merged cell is in the way, select the range and choose Format ▸ Merge cells ▸ Unmerge, then let the formula expand. Expected result: the #REF! disappears and the values fill down from the formula cell.

Make a one-row formula spill down the whole column

For the "only row 2 calculates" case, wrap the operation in ARRAYFORMULA and use open-ended ranges so new rows are covered:

=ARRAYFORMULA(A2:A * B2:B)

Google's reference describes the argument as "a range, mathematical expression using one cell range or multiple ranges of the same size, or a function that returns a result greater than one cell." That same-size rule matters: A2:A * B2:B10 mixes an open range with a fixed one and errors. Guard blank rows with a condition so empty inputs don't return zeros:

=ARRAYFORMULA(IF(A2:A="", "", A2:A * B2:B))

A real case: a header typed into the spill range

You put =SORT(FILTER(A2:D, C2:C>0), 1, TRUE) in F2 to build a live table. It shows #REF! — Array result was not expanded because it would overwrite data in F8. You scroll down and find a note someone typed into F8 weeks ago. You cut that note to another sheet, and the instant F8 is empty the sorted table spills from F2 down past F8 with no error. The formula was correct the whole time; it just needed the room.

Verify the spill and keep it from recurring

Click the formula cell: a correct spill highlights the whole filled block, and COUNTA() over the range now matches the number of rows the formula produced. To keep it working: put array formulas at the top of a dedicated, otherwise-empty column so nothing can grow into their spill range; avoid merged cells in any region a formula feeds; and prefer open-ended ranges (A2:A) over fixed ones so added rows stay covered. If collaborators keep typing under a spill, protect the range with Data ▸ Protect sheets and ranges.

How this differs from Excel's #SPILL! and a #N/A

The idea matches Excel, but the surface differs. Excel blocks a dynamic-array spill with #SPILL! and the note "Spill range isn't blank"; Google Sheets uses #REF! with "Array result was not expanded." Both mean the result had nowhere to land, but they're different products with different error tokens, so a control written for one — Excel's spill anchor, for instance — isn't there in the other. And neither is a #N/A: that means a lookup found no match, a value problem, not a spill-room problem.