What's happening
You typed an ARRAYFORMULA and instead of filling the column with results, Google Sheets shows a small error triangle. Hover over it and you see:
Array result was not expanded because it would overwrite data in B2.
The formula stops cold. The cell reference in the message (B2, C5, D10, etc.) changes depending on where the conflict is, but the root cause is always the same: something is already occupying a cell that the array needs to write into.
Why this happens
ARRAYFORMULA needs a completely clear path to spill its results. If even one cell in that range contains anything — a value, another formula, or even a stray space — Sheets refuses to expand. It won't silently overwrite your data, which is actually the right call, but it does leave you with a broken formula.
Common triggers:
- A header row or label typed directly in the output column
- Old data left over from manual entry
- Another formula already occupying a cell in the spill range
- A cell that looks blank but contains a space character (
" ") - A second ARRAYFORMULA in the same column competing for the same cells
Step 1 — Find the blocking cell
The error message points you straight to the problem. If it says "would overwrite data in B2", click B2 and check the formula bar. That said, Sheets only reports the first conflict — there may be more blockers further down the column.
To scan the entire output range quickly:
- Click the cell with your ARRAYFORMULA (e.g., B1).
- Press Ctrl + Shift + End (Windows/Linux) or Cmd + Shift + End (Mac) to jump to the last used cell in the sheet.
- Visually inspect the column for any non-empty cells.
Rather than eyeballing it, press Ctrl + F, enable "Search using regular expressions", and search for .+ within your target column. Every cell with content will surface immediately.
Step 2 — Clear the blocking cells
Select the occupied cells and press Delete — not Backspace. Delete clears content in place without shifting rows, which is what you want here.
Got a header you want to keep? Move it above the array's output range or shift it to a different column, then adjust the formula to match.
Watch out for hidden spaces. A cell can look completely empty and still contain " ". Click it and check the formula bar — if you see anything at all, delete it.
Step 3 — Fix or restructure the formula
Clear the path and your ARRAYFORMULA should expand on its own. Still broken? The formula's range is probably scoped incorrectly.
Basic example — multiply a column by 1.1
=ARRAYFORMULA(A2:A100 * 1.1)
This writes results into B2:B100. All 99 of those cells must be empty before you enter the formula in B1.
Using an open-ended range
=ARRAYFORMULA(IF(A2:A="", "", A2:A * 1.1))
The IF(A2:A="", "", ...) wrapper stops the array from writing empty strings infinitely down the column. Skip it and Sheets spills blank values into thousands of rows — which can itself cause conflicts if anything exists far below.
Keeping a header in the same column
Want a "Price" label in B1 with array output starting at B2? Put the ARRAYFORMULA in B2, not B1:
B1: Price
B2: =ARRAYFORMULA(IF(A2:A="", "", A2:A * 1.1))
The header lives above the array's range, so there's no conflict.
Combining a header inside the formula
Some people prefer one formula that handles both the header and the data:
=ARRAYFORMULA({"Price"; IF(A2:A="", "", A2:A * 1.1)})
The {"Price"; ...} syntax builds a vertical array — row 1 is the header string, the rest is calculated data. Drop this in B1 and leave everything below it empty.
Step 4 — Handle the "two ARRAYFORMULAs in one column" case
Two ARRAYFORMULA entries competing for overlapping rows? One will always lose. Merge them into a single formula, or partition the rows so the ranges never touch:
=ARRAYFORMULA(IF(ROW(A:A)=1, "Header", IF(A:A="", "", A:A * 1.1)))
One formula, one source of truth. Row 1 gets the header; every other row gets the calculation.
Verification
After clearing the blocking cells and adjusting the formula:
- The error triangle disappears from the formula cell.
- Results fill the column automatically as you add data in the source column.
- Click any cell in the output range — a light-blue border highlights the entire spill range, and the formula bar shows the parent formula grayed out (read-only, because it's array-controlled).
- Type a new value in the source column and watch the result appear instantly — no manual action needed.
Lessons learned
- The array owns its output range. Never type directly into cells it controls. Your entry will break the array and trigger the same error the next time the formula recalculates.
- Plan column layout before writing the formula. Decide which column the array will occupy, then leave it completely empty except for the formula cell itself.
- Wrap open-ended ranges with IF. Ranges like
A2:Aneed anIF(A2:A="", "", ...)guard. Without it, the array floods every row in the sheet with blank values. - One ARRAYFORMULA per column. Multiple competing arrays in the same column are a maintenance headache. Consolidate them into a single formula.