How to Fix ‘Array Result Was Not Expanded’ (#REF!) in Google Sheets

Working in Google Sheets with dynamic formulas like ARRAYFORMULA, FILTER, QUERY, SORT, or SPLIT only for the formula cell to display the red flag error: “#REF! Array result was not expanded because it would overwrite data in [Cell_Coordinate]” halts automated calculations. In Google Sheets’ calculation model, array functions calculate their output dimensions in memory before writing values down rows and across columns on the grid. If even a single cell within that designated destination range contains existing text, a numeric value, a zero, a space character, or an empty apostrophe ('), Google Sheets halts the entire array expansion to protect your existing spreadsheet data from accidental overwrites. This guide explains how to locate hidden obstructing data, clear array paths, and handle expanding formulas in Google Sheets.

Why Does Google Sheets Block Array Expansion?

Unlike traditional desktop spreadsheets that silently truncate results, Google Sheets enforces non-destructive array rendering. Common causes of this error include:

  • Invisible Characters in the Destination Grid: Trailing spaces or empty strings left behind after manual data deletion or copy-pasting from web portals.
  • Merged Cells in the Path: Merged cell blocks that span across rows or columns required by the expanding array.
  • ArrayFormula Range Collisions: Stacking two dynamic array formulas in the same column where the upper formula expands downwards into the lower formula’s cell.
  • Full-Column Formula References (e.g. A:A): Using unbounded ranges like A2:A that calculate output down to row 1,000,000 when summary totals sit at the bottom of the column.

Method 1: Identify and Clear the Blocking Cell

The error tooltip reveals the precise cell coordinate preventing the formula from rendering.

  1. Hover your mouse cursor over the cell displaying the #REF! error.
  2. Read the tooltip message: “Array result was not expanded because it would overwrite data in [X]” (for example, B14).
  3. Navigate directly to the cited cell coordinate.
  4. Notice what is in the cell. Even if it looks completely blank, click on it and check the formula bar—it frequently contains a single invisible space or formatting mark.
  5. Press the Delete key to clear the obstructing cell.
  6. The moment the cell is emptied, the array will expand and populate the grid immediately.

Method 2: Clear Trailing Rows Below the Formula

If an array formula calculates multiple rows of data, clearing all cells below the formula ensures adequate expansion headroom.

  1. Click on the cell immediately below your array formula.
  2. Press Ctrl + Shift + Down Arrow (or Command + Shift + Down Arrow on Mac) to select all cells down to the bottom of the worksheet.
  3. Press the Delete key.
  4. If an obstructing cell was located further down the column, deleting the trailing range immediately clears the expansion path.

Method 3: Unmerge Cells in the Calculated Array Range

Google Sheets cannot write multi-cell array data into merged cell blocks.

  1. Inspect the columns and rows where your array formula is expanding.
  2. Look for any cells that have been merged horizontally or vertically.
  3. Select the merged cells, click the Merge cells icon in the toolbar, and choose Unmerge.
  4. Re-evaluate your formula. The array will stream its output cleanly into individual cells.

Method 4: Constrain Unbounded Array Ranges

If your formula uses open-ended column references like ARRAYFORMULA(IF(A2:A="","",...)), ensure that blank rows are filtered out.

  1. Wrap your array calculation inside a FILTER function so it only processes populated rows:
    =FILTER(A2:A*B2:B, A2:A<>"")
  2. This restricts the calculated array height to your exact data row count, preventing formulas from attempting to expand into bottom summary rows.

Get the best tech tips delivered straight to your inbox.

Join thousands of readers mastering Apple, Google, Microsoft, and Linux.