Google Sheets / 5 min read
How to Fix #REF! Error in Google Sheets
A plain-English fix guide for #REF! errors in Google Sheets, including broken references, deleted columns, and array spill issues.
Error text: #REF!
A #REF! error in Google Sheets usually means the formula points to a cell, range, or result location that no longer works. Start by checking deleted columns, moved ranges, and spill conflicts.
Check this first
- Check whether a referenced column, row, or sheet was deleted.
- Click the formula and inspect each range highlight one by one.
- Look for another value blocking an expanding array result.
- If the formula uses IMPORTRANGE, confirm the source range still exists.
Working examples
Catch the error while keeping the sheet readable
=IFERROR(VLOOKUP(A2, Sheet2!A:B, 2, FALSE), "")Why #REF! happens
Google Sheets shows #REF! when a formula reference becomes invalid. That often happens after someone deletes a column, moves data, or pastes a formula that tries to return results into occupied cells.
The key is that #REF! is not one single problem. It is a family of reference problems, so the fastest fix is to find what changed in the source range or output range.
Most common fixes
If the error started after editing the layout, undo the structural change first. If that is not possible, rebuild the reference manually by reselecting the intended range in the formula editor.
- Restore deleted columns or rows if the formula depended on them.
- Replace broken references with the new range manually.
- Clear cells that block an array result from expanding.
- Wrap expected lookup misses with IFERROR after the reference itself is valid.
When IFERROR does not help
IFERROR can hide the message, but it does not repair a broken model. If the wrong range is referenced, the sheet may look clean while still returning bad data. Fix the reference first, then decide whether hiding expected misses improves usability.