Google Sheets 陣列無法展開，因為會覆蓋資料：排查與修復

Tested in: Live formula checks in Microsoft Excel 16.114.4 for Mac (English UI; system region zh-Hans_US), November 8, 2026; Google Sheets (spreadsheet locale zh_CN), November 9, 2026. Results and platform differences are noted below; this is not a certification of every menu workflow.

Which versions this applies to

Google Sheets (any formula that spills: ARRAYFORMULA, FILTER, QUERY, SPLIT, UNIQUE, SORT, SEQUENCE, IMPORTRANGE, BYROW, MAP and so on). Excel’s equivalent is #SPILL! “Spill range isn’t blank”. See the Excel section below.

Example sheet to reproduce the error

Type this into a blank sheet. Column D is meant to be filled by one formula in D2.

D2 now shows #REF! with “Array result was not expanded because it would overwrite data in D5.” Delete the 50 in D5 and the totals for all four rows appear.

Step 1: Find the blocking cell

1. Click the #REF! cell and read the hover message. The cell it names is the first blocker Sheets found. There may be more further down.
2. Click that cell and look at the formula bar. If it shows a value or a formula, that’s your blocker.
3. If the formula bar looks empty, check for hidden content in a spare cell:

=ISBLANK(D5)

=LEN(D5)

ISBLANK returns FALSE and LEN returns 0 when the cell holds an empty string, so it isn’t truly empty. LEN returns 1 or more if it holds spaces.

4. To check the whole output area at once, count what’s in it (adjust to your range, starting below the formula cell):

=COUNTA(D3:D)

Google’s documentation says COUNTA counts every value, including zero-length strings and whitespace. That makes it a good way to find “invisible” blockers. You want this to return 0 before you enter the array formula.

Step 2: Fix it, cause by cause

Cause 1: A value was typed or pasted into the output range

This is the most common cause. Someone typed a number over one of the results, or pasted a block of data over the column.

How to detect: the named cell shows a normal value in the formula bar.

How to fix: select the cell (or the whole output range, e.g. D3:D) and press Delete. Array formulas in current Google Sheets never overwrite static values. A Google Docs Editors Community answer notes that classic Sheets did this until around 2014, but not anymore. There’s no setting to change that.

Cause 2: “Empty” cells that aren’t empty

How to detect: =ISBLANK(cell) returns FALSE even though the cell looks blank. Typical sources:

• a space typed into the cell
• an empty string "" left behind after you Paste special > Values only on a formula like =IF(A2="","",...)
• data pushed in by an external tool or script that writes "" into cells (a case from the Google Docs Editors Community: the cells “may look blank but they actually contain zero-length text strings”)

How to fix: select the whole output area and press Delete. Don’t just delete the one cell the error names; there are often hundreds more just like it.

Cause 3: Checkboxes (or other values) sitting in the output area

How to detect: the output column contains checkboxes. An unchecked checkbox isn’t empty; it holds the value FALSE.

How to fix: remove the checkboxes from the output area (select them and press Delete), or move the array formula to a column without checkboxes.

Cause 4: Two spilling formulas collide

How to detect: turn on View > Show > Formulas (or press Ctrl + `) and look for a second formula inside the output area. Common versions:

• the same ARRAYFORMULA was dragged down, so D3 also contains a formula
• a SPLIT returns several columns and runs into the next formula to its right
• two FILTER or QUERY results are stacked too close together, and the top one grows into the bottom one when new data arrives

How to fix: keep only one formula at the top of each spilled block. For SPLIT, leave enough empty columns to the right for the maximum number of parts. For stacked results, place them side by side or on separate tabs, or combine them into one array:

={FILTER(A2:C, C2:C="Open"); FILTER(A2:C, C2:C="Pending")}

If one of the FILTERs finds no rows, it returns a single #N/A cell, and the combined array fails because that part has one column instead of three. Give each FILTER a fallback with the same number of columns:

={IFERROR(FILTER(A2:C, C2:C="Open"), {"","",""}); IFERROR(FILTER(A2:C, C2:C="Pending"), {"","",""})}

Cause 5: A totals row or note below an open-ended range

How to detect: your formula uses an open-ended range like A2:A, and somewhere far down the column (often the very last row) there’s a “Total” row, a note, or a stray formula. IF(A2:A="", "", ...) writes an empty string into every row down to the bottom of the sheet. Those empty results still occupy cells, so anything below blocks the formula.

How to fix: pick one of these:

• Move the total above the data, e.g. in D1 use =SUM(D3:D) and put the header in row 2.
• Use a bounded range that stops before the totals row:

=ARRAYFORMULA(IF(A2:A999="", "", B2:B999*C2:C999))

• Use FILTER, which only outputs as many rows as it finds. Note that it skips blank rows, so the results only line up with your data if there are no gaps in column A:

=FILTER(B2:B*C2:C, A2:A<>"")

Cause 6: You want to override some results by hand

How to detect: you (or a colleague) keep typing corrections over a few formula results, which breaks the whole column every time.

How to fix: you can’t edit an array formula’s output, but you can add an override column and let the formula prefer it. Add a column E called “Manual total”, then in D2:

=ARRAYFORMULA(IF(A2:A="", "", IF(E2:E<>"", E2:E, B2:B*C2:C)))

Now anyone can type into column E, and column D shows the manual value where one exists and the calculated value everywhere else.

Less common: merged cells, cell limits, and IMPORTRANGE

• Merged cells: inspect the output area and any stored values. Blank merged cells did not block the live Sheets example; do not assume Excel’s merged-cell #SPILL! rule applies identically. To simplify the layout while troubleshooting, choose Format > Merge cells > Unmerge.
• A different #REF! message, “Result was not automatically expanded because the spreadsheet may exceed the 10,000,000 cell limit”, is a size problem, not a blocker. The community troubleshooting guide suggests deleting unused empty rows or columns elsewhere in the file.
• For IMPORTRANGE, the community guide notes that Sheets automatically adds 500 rows when the output needs more rows than the sheet has. That’s expected behavior.

Prevent it from coming back

1. Label the column. Put the formula in the header row, e.g. ={"Total"; ARRAYFORMULA(IF(A2:A="", "", B2:B*C2:C))} in D1, so it’s obvious the column is formula-owned.
2. Protect the output range. Use Data > Protect sheets and ranges to stop people typing into it.
3. Color the column (light grey background) and add a note: “Calculated. Don’t type here.”
4. Leave buffer columns to the right of SPLIT, QUERY and IMPORTRANGE outputs whose width might grow.

Excel vs. Google Sheets

Excel’s dashed border around the intended spill range is handy: it shows exactly how far the result wants to go. Sheets has no such preview, so the COUNTA check from step 1 is your best substitute. For the table-specific Excel case, see Excel #SPILL! Error Inside a Table: Why It Happens and 3 Workarounds.

Common mistakes

1. Deleting only the cell named in the error. The message names the first blocker. Clear the whole output range.
2. Pressing Space to “clear” a cell. That puts a space in the cell, which blocks the formula.
3. Dragging an ARRAYFORMULA down. One formula at the top is enough; copies below it are blockers.
4. Pasting values over a formula column to “freeze” it, then re-adding the formula. The pasted values, including "", block it.
5. Using open-ended ranges with a totals row at the bottom. Bound the range or move the total up.

FAQ

Can I make ARRAYFORMULA overwrite existing data?

No. In current Google Sheets, array formulas never overwrite static values and there’s no setting to change that. Clear the output area, or use an override column that the formula reads from.

The cell looks empty. Why does Sheets say it would overwrite data?

The cell probably contains a space, an empty string from pasted formula results, or an unchecked checkbox (which holds FALSE). Check with =ISBLANK(cell) and =LEN(cell), then select the range and press Delete.

How do I find all blocking cells at once?

Use =COUNTA on the output range below the formula, for example =COUNTA(D3:D). Google’s docs say COUNTA counts zero-length strings and whitespace, so anything above 0 means there’s still something to clear.

Is this the same as Excel’s #SPILL! error?

It’s the same idea. Excel shows #SPILL! “Spill range isn’t blank” and offers Select Obstructing Cells to jump to the blockers.

Related tutorials

• FILTER vs QUERY in Google Sheets: Which One Should You Use? (cluster hub)
• ARRAYFORMULA vs MAP/LAMBDA vs Fill Down in Google Sheets
• Fix “You Need to Connect These Sheets” IMPORTRANGE #REF! Error
• Google Sheets QUERY “Unable to Parse Query String” Error: Causes and Fixes
• Excel #SPILL! Error Inside a Table: Why It Happens and 3 Workarounds

Live-test findings

Native Sheets returned #REF! when the spill would overwrite a typed value, a zero-length-string formula, or a checkbox storing FALSE. A blank checkbox validation rule without a stored value did not block it. The error identified the blocking cell, for example: “Array result was not expanded because it would overwrite data in D5.” Clearing the stored blocker allowed expansion. Blank merged cells did not block the tested Sheets case; Excel merged-cell behavior differs.

Sources

• Google Docs Editors Help: ARRAYFORMULA: https://support.google.com/docs/answer/3093275
• Google Docs Editors Help: COUNTA (counts zero-length strings and whitespace): https://support.google.com/docs/answer/3093991
• Google Docs Editors Help: Add & use checkboxes: https://support.google.com/docs/answer/7684717
• Google Docs Editors Community thread: “Array result was not expanded because it would overwrite data” (zero-length text strings as hidden blockers): https://support.google.com/docs/thread/235274900
• Google Docs Editors Community thread: “Sheets ArrayFormula overwrite behavior has changed” (array formulas never overwrite static values): https://support.google.com/docs/thread/180006904
• Google Docs Editors Community guide: “Resolving IMPORTRANGE Errors & Issues” (10,000,000 cell limit message, 500 added rows): https://support.google.com/docs/community-guide/453661366/resolving-importrange-errors-issues
• Microsoft Support: How to correct a #SPILL! error: https://support.microsoft.com/en-us/office/how-to-correct-a-spill-error-ffe0f555-b479-4a17-a6e2-ef9cc9ad4023