How to Fix ARRAYFORMULA Not Expanding in Google Sheets
Freelancers, solo operators, and very small business owners using Google Sheets as a practical business operating system.
On this page
When ARRAYFORMULA stops expanding its output into the cells below or beside it, do not start rewriting the formula. That is how a small spreadsheet problem becomes a business-system problem. Preserve the file first, isolate the failure, then change only the smallest part you can prove is wrong.
ARRAYFORMULA needs empty cells to spill into, so the cause is usually not the formula at all. It is often a blocked cell, a header in the wrong row, a manual edit sitting in the output area, or a copied template that inherited the problem. The fix depends on which one you are actually looking at.
If this is one of several things breaking in the same file, use the broader Google Sheets troubleshooting guide first, then come back to this specific repair path.
Make one rollback copy before you touch anything#
Make a duplicate or version-safe copy before risky edits. You do not need a perfect backup ritual; you need one clean rollback point before you touch formulas, protected ranges, source lists, imported data, or dashboard ranges.
Then write the symptom in one sentence: what changed, where it changed, and what the sheet should have done instead. That sentence keeps you from treating every spreadsheet issue like a formula issue.
Find the blocked cell before editing the formula#
Use this diagnosis table before editing anything complicated. Your job is to identify the layer that failed, not to guess a fix because a forum answer sounded plausible.
| What you see | Likely cause | Safest first fix |
|---|---|---|
| Result will not expand | Output cells already contain content | Clear the spill range after backing up values |
| Only first row works | Formula is not written for an array range | Test formula logic on a small range |
| Header disappears | Formula starts where the header should live | Put header in row 1 and formula below |
| Manual edits vanish | Users typed inside formula output area | Move manual inputs to separate columns |
| Template feels fragile | Array controls too many downstream cells | Use helper columns and clear ownership rules |
Test the smallest broken part, not the whole output#
Now test the smallest visible part of the problem. If it is a formula, test one referenced cell or one row. If it is a dropdown, inspect one validation rule. If it is a protected range, check one blocked cell and its owner. If it is formatting, check whether the value is really a date or number before changing the display style.
This is where most rushed fixes go wrong: they edit the final visible output while the actual failure is upstream. For formula issues, the guide on finding which cell is breaking a Google Sheets formula is the safer path.
Check what a shared file is hiding before you delete rows#
Shared templates add another layer: other people may be using filters, protected ranges, or views that change what you see without changing the underlying data. Before you delete rows or overwrite formulas, check whether you are looking at a filtered view, a locked range, or a copied template with inherited settings.
If the problem appeared right after copying a template, pause and compare it with a clean original. The copy process itself may have changed sharing, validation, protected ranges, or references. See how to make a copy of a Google Sheets template without breaking it before rebuilding the file from scratch.
Do not let IFERROR hide the cell you need to fix#
When official Google documentation defines a function or feature, use that behavior as the boundary. A workaround is fine only if you understand what it is bypassing. IFERROR, for example, can make a sheet calmer for users, but it also hides the broken cell you actually need to repair.
For business files, keep a temporary debug copy where errors stay visible. Masking errors is the last polish step, not the first troubleshooting move.
The safe repair sequence: one edit at a time#
A safe repair sequence looks like this: duplicate the file, reproduce the issue in one place, isolate the input or setting, make one edit, test with a realistic sample row, then document the change. If you cannot reproduce the problem in a small test, do not make a large change to the live sheet.
For formulas specifically, start with the plain-English Google Sheets formulas guide if the syntax itself is unclear. Guessing at functions while a live template is broken is slower than learning the one pattern you need.
Stop it happening again: separate your layers#
Prevention is mostly structure. Keep raw data separate from formulas. Keep formulas separate from dashboard outputs. Keep source lists for dropdowns on a clearly named tab. Protect formula cells, but make input cells obvious. Add a small notes tab that explains which ranges are safe to edit.
The goal is not to make the sheet impossible to break. The goal is to make the next break obvious enough that a tired operator can fix it without opening ten tabs and hoping.
Keep this checklist open while you repair#
This is the table to work from, not just read. Do not jump straight from symptom to fix; confirm the middle column first, then apply the safest fix.
| What you see | Likely cause | Safest first fix |
|---|---|---|
| Result will not expand | Output cells already contain content | Clear the spill range after backing up values |
| Only first row works | Formula is not written for an array range | Test formula logic on a small range |
| Header disappears | Formula starts where the header should live | Put header in row 1 and formula below |
| Manual edits vanish | Users typed inside formula output area | Move manual inputs to separate columns |
| Template feels fragile | Array controls too many downstream cells | Use helper columns and clear ownership rules |
What to do next#
If the sheet is business-critical, make the smallest repair in a duplicate, test it with realistic sample data, and apply it to the live version only after the symptom is gone. If the issue turns out to be formula-related, continue with how to fix formula parse error in Google Sheets or how to fix VLOOKUP returning N/A in Google Sheets, depending on what you see.
Spreadsheet confidence comes from a repeatable repair loop: preserve, isolate, test, fix, document. That loop beats almost every one-line answer pasted from a random thread.
We aim to keep this accurate and date-stamp it when product steps change. We don't claim hands-on product testing we haven't done, and we'll always point to the free, native route first. How we work.