ARRAYFORMULA #REF! "would overwrite data" — what it means and how to stop it
The error reads something like: the formula "would overwrite data" in a named cell. It means your ARRAYFORMULA is trying to spill results into cells that are no longer empty. Something, often someone, put a value in its way.
Clear it
- Read the error: it names the blocking cell. Go there.
- Decide what the blocking value is. If it is a stray typed value, delete it and the formula recovers on its own.
- If the blocking value matters, move it elsewhere. Array formulas need empty spill room below (and sometimes to the side of) the formula cell.
- Check the whole spill column for other strays while you are there.
Stop it happening again
- Leave the spill area visibly empty. A note in the top cell ("auto-filled, do not type below") helps.
- Protect the spill range (Data → Protect sheets and ranges) so only keepers can write there.
- Keep array formulas on their own columns where possible, away from columns people type into.
Catch spill damage early
Shared Sheet Guardrails™ checks a Google Sheets™ file for broken array formulas, typed-over cells, lost validation and the rest. Checking any sheet is free, and you can take guardrails off in one click.
Install from the Google Workspace Marketplace™
Google, Google Sheets™ and Google Workspace™ are trademarks of Google LLC. App Garden™ is not affiliated with or endorsed by Google.