Fixes / Google Sheets · Checked

Google Sheets "Array result was not expanded": how to fix it

Short answer

This #REF! error means a formula that returns several cells, such as ARRAYFORMULA, FILTER, QUERY, UNIQUE, SORT or IMPORTRANGE, has no room to write its results because at least one cell in its output area has something in it. Hover over the error to see which cell is blocking it, select that cell and press Delete. The results then appear right away.

What this message means

Some Google Sheets formulas return a whole block of values from one cell, and Sheets fills ("expands" or "spills") the results into the cells below and to the right. Sheets will not overwrite anything already in those cells. If even one cell in that area holds a value, a formula, a space, a checkbox or part of a merged cell, the formula shows #REF! instead of its results. Hover over the formula cell to see the full message, which ends with the address of the first blocking cell, for example "would overwrite data in B7". Your formula is not wrong. It just has no room.

Common causes

CauseHow to tell
Someone typed a value into a cell inside the formula's output areaThe cell named in the error (for example B7) shows a visible value, often one that was typed over an old result. The formula cell shows #REF!, and the column below it is empty apart from that value.
A cell looks empty but actually holds something (a space, an empty string, white text or a formula returning "")The named cell looks blank, but when you click it the formula bar shows a space, an apostrophe, a formula, or the text turns out to be white on white. Pressing Ctrl+Down arrow (Cmd+Down arrow on Mac) from the formula cell stops on that cell instead of jumping to the bottom.
Old per-row formulas were copied down below an ARRAYFORMULAClicking cells under the ARRAYFORMULA shows their own formulas (such as =B3*C3) in the formula bar. This often happens after dragging a formula down or accepting Sheets' autofill suggestion.
Checkboxes or merged cells sit in the output areaThe blocking cell has a checkbox (it holds TRUE or FALSE even when unticked), or clicking it selects a larger merged block and Format > Merge cells shows the Unmerge option.
Hidden rows, hidden columns or a filter hide the blocking cellRow numbers or column letters skip (for example row 12 is followed by 15), or a filter icon is shown in the header row. The cell named in the error cannot be seen on screen.
An import, form, add-on, script or API writes new rows into the formula's output areaThe error comes back by itself after new data arrives (a bank feed, Apps Script, Zapier or an API append), even though you already cleared the cell once.

How to fix it

1. Clear the cell named in the error

  1. Hover over the cell showing #REF! (on phone or tablet, tap it) and read the full message. It ends with a cell address, for example "would overwrite data in B7".
  2. Click that cell, or type the address in the Name box to the left of the formula bar and press Enter.
  3. Press Delete or Backspace to clear it, even if it already looks empty.
  4. If the error now names a different cell, clear that one too. To do it all at once, select the whole area under and beside the formula (not the formula cell itself) and press Delete.

2. Find cells that look empty but are not

  1. Click the formula cell, then press Ctrl+Down arrow (Windows, ChromeOS) or Cmd+Down arrow (Mac). The cursor stops on the next cell that has content.
  2. Check the formula bar for that cell. A space, an apostrophe or a formula such as ="" all count as data.
  3. Select the cell and press Delete. Do the same with Ctrl+Right arrow (Cmd+Right arrow on Mac) if the formula also spills sideways.
  4. Click the formula cell again. The results should fill in right away.

3. Remove leftover formulas under an ARRAYFORMULA

  1. Keep the ARRAYFORMULA only in the top cell of the column (usually row 2, under the header).
  2. Select every cell below it in that column, from the row under the formula to the last row with data.
  3. Press Delete. The ARRAYFORMULA now fills those rows itself.
  4. If Sheets later suggests "autofill" for that column, dismiss the suggestion so it does not copy formulas back in.

4. Unmerge cells, remove checkboxes and show hidden rows

  1. Select the output area and choose Format > Merge cells > Unmerge.
  2. If there are checkboxes, select those cells, go to Data > Data validation, and remove the checkbox rule, then press Delete to clear the TRUE or FALSE values.
  3. Select the whole sheet (Ctrl+A, or Cmd+A on Mac), right-click a row number and choose Unhide rows, and do the same for columns if needed.
  4. Turn off any filter with Data > Remove filter, then clear any cells you now see in the output area.

5. Give the formula room or limit how much it returns

  1. If you need to keep the data that is in the way, cut the formula (Ctrl+X, or Cmd+X on Mac) and paste it into an empty column or a new tab.
  2. Limit an open range like A2:A to a fixed range such as A2:A500 so the result cannot run into data further down.
  3. To cap the size of a result, wrap it in ARRAY_CONSTRAIN, for example =ARRAY_CONSTRAIN(FILTER(A2:C, C2:C>100), 50, 3) to return at most 50 rows and 3 columns.
  4. Check that the formula cell no longer shows #REF!.

6. Keep automated data away from the output area

Sheets filled by imports, scripts, add-ons, Google Forms or the Sheets API

  1. Find what writes into the sheet (for example an Apps Script, Zapier, a bank-feed tool, or an API that appends rows).
  2. Move your formula to a separate tab and point it at the raw data, for example =FILTER(Data!A2:D, Data!D2:D<>"").
  3. Do not add manual notes or values in columns the formula fills. Put them in a column the formula does not touch.
  4. If a script must write next to the formula, change it to write into columns outside the formula's output range.

Next time, skip the search

Crade is an AI assistant for Mac and Windows that sees your screen. When a message like this pops up, ask “what does this mean?” and it explains the exact error in front of you, step by step.

Download Crade

Still not fixed?

Make a copy of the sheet (File > Make a copy) and, in the copy, delete every row below the formula cell. If the error disappears, the blocker was in those rows, so undo and delete them in smaller groups to find it. If the error stays even with the output area fully empty, the formula is probably returning more rows or columns than the sheet has, so add rows at the bottom or limit the range. For sheets built by an add-on or template (such as budgeting tools), check that vendor's help or community forum, because their formulas often expect you to type only in specific cells. For a Google Workspace account, your admin can open a case with Google Workspace support.

Questions people ask

Why does it say it would overwrite data when the cell looks empty?

The cell probably holds something you cannot see: a space, a formula that returns an empty string, white text, or a checkbox with a FALSE value. Select the cell and press Delete, which removes those.

Will IFERROR fix the array result not expanded error?

No. IFERROR does not make room for the results, so the data still will not appear. Clear or move whatever is in the output area instead.

Can I make the formula overwrite the existing data?

No. Google Sheets never lets a formula overwrite cells that have content. Either delete that content or move the formula somewhere with enough empty space.

Is this the same as the #SPILL! error in Excel?

Yes, it is the same problem. Excel shows #SPILL! when a dynamic array formula is blocked, and Google Sheets shows #REF! with the "Array result was not expanded" message. The fix is the same in both apps: clear the cells that block the output.

Why does the error keep coming back after I fix it?

Something keeps writing into the output area, usually a person typing in those cells, a copied-down formula, or a script, form or import adding rows. Move the formula to its own tab or column so new data cannot land in its output range.

Sources

Related errors