Google Sheets ARRAYFORMULA Expansion Issues & How To Fix Them

Your ARRAYFORMULA isn’t expanding, either you see “Array result was not expanded” (#REF!), or the formula only fills one cell instead of the entire range.
This usually happens because the output range is blocked or the formula structure isn’t array-compatible.

This guide fixes it step by step.

Why the Issue Happens

• Output cells already contain data (blocking expansion)
• Merged cells in the output range
• Incorrect formula structure (not using ranges)
• Mismatched array sizes
• Using functions that don’t support arrays
• Hidden values or formatting in cells
• Overlapping multiple ARRAYFORMULA outputs
• Full-column conflicts in large sheets

Step-by-Step Fixes

Step 1: Clear the Output Range

Most common cause of #REF!.

Error:

Array result was not expanded because it would overwrite data

Fix:

• Delete all cells below/right of the formula
• Ensure the entire expansion range is empty

Step 2: Remove Merged Cells

ARRAYFORMULA cannot expand across merged cells.

Fix:

• Select output range
• Format → Merge cells → Unmerge

Step 3: Use Proper Range-Based Formula

Incorrect (single cell):

=ARRAYFORMULA(A2 * B2)

Correct:

=ARRAYFORMULA(A2:A * B2:B)

Always use ranges, not individual cells.

Step 4: Match Array Sizes

Ranges must align.

Wrong:

=ARRAYFORMULA(A2:A100 * B2:B50)

Correct:

=ARRAYFORMULA(A2:A100 * B2:B100)

Step 5: Handle Blank Rows Properly

Without handling blanks, formula may behave inconsistently.

Fix:

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

Prevents unnecessary expansion errors.

Step 6: Avoid Overlapping ARRAYFORMULA

Only one array formula should control a column.

Problem:

• Two ARRAYFORMULA formulas targeting same range

Fix:

• Keep only one
• Move others to different columns

Step 7: Avoid Unsupported Functions

Some functions don’t work well with ARRAYFORMULA.

Example issue:

=ARRAYFORMULA(VLOOKUP(A2:A, A:B, 2, FALSE))

Fix:

=ARRAYFORMULA(IFERROR(VLOOKUP(A2:A, A:B, 2, FALSE)))

Or use:

=MAP(A2:A, LAMBDA(x, VLOOKUP(x, A:B, 2, FALSE)))

Step 8: Limit Full Column Usage

Using full columns can cause expansion conflicts.

Problem:

=ARRAYFORMULA(A:A * B:B)

Fix:

=ARRAYFORMULA(A2:A1000 * B2:B1000)

Step 9: Check for Hidden Data

Cells may appear empty but contain invisible values.

Test:

=LEN(A2)

If >0 → not empty.

Fix:

• Clear contents completely

Step 10: Re-enter Formula

Sometimes expansion glitches occur.

Fix:

• Delete formula
• Re-enter it cleanly

This resets array behavior.

Common Mistakes

• Not clearing output range before using ARRAYFORMULA
• Using single-cell references instead of ranges
• Mismatched range sizes
• Overlapping multiple array formulas
• Ignoring merged cells
• Using unsupported functions directly
• Using full-column ranges in large datasets

Pro Tips / Better Alternatives

Use IF to Control Expansion

=ARRAYFORMULA(IF(A2:A="", "", A2:A * 10))

Prevents unnecessary output.

Use MAP for Advanced Logic

=MAP(A2:A, B2:B, LAMBDA(a, b, a * b))

More flexible than ARRAYFORMULA.

Use BYROW for Row-Based Calculations

=BYROW(A2:C100, LAMBDA(row, SUM(row)))

Cleaner for row operations.

Use Helper Columns for Complex Logic

Break formulas:

• Column B → intermediate
• Column C → final

Reduces expansion issues.

Optimize Large Sheets

• Limit ranges
• Avoid nested ARRAYFORMULA
• Reduce volatile functions

Bottom Line

If ARRAYFORMULA isn’t expanding, fix in this order:

1. Clear output range (remove blocking data)
2. Remove merged cells
3. Use proper range-based formulas
4. Match array sizes
5. Avoid overlapping formulas
6. Limit full-column usage

Most issues come from blocked expansion or incorrect formula structure.
Fix those, and ARRAYFORMULA will expand correctly and reliably.