How to Find and Fix Broken Formulas in Google Sheets
Formula errors in Google Sheets always seem to appear at the worst moment — right before you share a report or hand off a model. The good news: each error code tells you exactly what went wrong, and once you know how to find every broken formula across a workbook (not just the one staring back at you), fixing them is fast and methodical.
What each Google Sheets error actually means
Before you can fix anything, read the error. Google Sheets uses a small set of codes, and each one points to a specific kind of problem.
- #REF! — a reference is broken. The cell, row, column, or sheet a formula pointed to no longer exists. Typical cause: you deleted a row, column, or tab that other formulas depended on.
- #NAME? — Sheets does not recognize a name in the formula. Usually a misspelled function (SUMM instead of SUM), a named range that does not exist, or text left unquoted.
- #VALUE! — the wrong type of argument. You are doing math on text, or feeding a function a value it cannot interpret — for example adding a cell that contains a word.
- #DIV/0! — division by zero (or by an empty cell). Common in ratios and averages before data is filled in.
- #N/A — a lookup found nothing. VLOOKUP, HLOOKUP, XLOOKUP, or MATCH could not find the search key in the range.
- #NUM! — a number problem: a calculation produced a value too large to handle, or a function got an impossible numeric argument (like the square root of a negative number).
- #NULL! — rare in Sheets; it appears when range references are combined incorrectly, often through a misplaced space between ranges.
- #ERROR! — a parse error. The formula syntax itself is malformed, frequently a stray comma, an unclosed parenthesis, or the wrong argument separator.
How to find broken formulas across a whole workbook
The hardest part is not fixing errors — it is finding all of them. Errors hide in unscrolled regions, in columns off the right edge of the screen, and in tabs you rarely open. A clean-looking summary tab can sit on top of three broken supporting tabs. Here is how to surface them systematically.
Search with Find and replace
Open Find and replace with Ctrl+H (or Cmd+Shift+H on a Mac), and search for the literal text #REF!, then repeat for #N/A, #VALUE!, and the others. One important setting: a plain find matches the displayed error, but if you want to catch references that are broken inside the formula text, enable Search within formulas. Also switch the search scope from "This sheet" to All sheets so you are not auditing one tab at a time.
Surface errors with a helper column
For a more reliable sweep, add a helper column next to your data and wrap a test around each row. ISERROR returns TRUE wherever a formula is broken — for example ISERROR(B2) — so you can then filter or sort that column to pull every error to the top. IFERROR works similarly when you want to label them: IFERROR(B2, "CHECK") flags the bad cells while leaving good ones untouched.
Filter and check every tab
Apply a filter to the helper column and filter for error values or your "CHECK" label to isolate problem rows instantly. Then do the unglamorous but essential step: walk through every tab, not just the ones you usually look at. Errors propagate — a #REF! on a hidden data tab quietly turns into a #N/A or #VALUE! three sheets downstream.
How to fix each type of error
Once you have located an error, the fix follows directly from the cause.
- #REF! — restore the deleted reference. Use Undo (Ctrl+Z) if you just deleted the row or column; otherwise repoint the formula to the correct cell or rebuild the missing source. Avoid retyping over the #REF! token without fixing the underlying reference.
- #NAME? — correct the spelling of the function, or define the named range under Data > Named ranges. Make sure any text values inside the formula are wrapped in quotation marks.
- #VALUE! — fix the argument types. Check that cells used in math contain numbers, not text or stray spaces; VALUE or TRIM can clean up numbers stored as text.
- #DIV/0! — guard the division. Wrap it: IFERROR(A2/B2, 0), or test first with IF(B2=0, "", A2/B2) so an empty denominator shows a blank instead of an error.
- #N/A — confirm the lookup key actually exists in the range and that both sides match exactly (watch for trailing spaces and number-vs-text mismatches). Wrap with IFERROR to show a friendly fallback only after you have confirmed the lookup is otherwise correct.
- #NUM! — check the math is possible and the inputs are in range; adjust the formula so it cannot be asked to compute an undefined result.
- #NULL! — fix the range operators, usually by replacing an accidental space with a comma or colon.
- #ERROR! — repair the syntax: balance parentheses, remove stray separators, and confirm you are using commas (or semicolons, depending on your locale) correctly.
How to prevent broken formulas
Most errors are avoidable with a few habits.
- Use named ranges instead of raw cell references where you can. Named ranges survive layout changes better and make formulas readable.
- Do not delete rows or columns blindly. Before removing anything, check what depends on it — deleting a column is the number-one source of #REF! errors.
- Add data validation to input cells so people cannot type text where a number belongs, heading off #VALUE! and #DIV/0! before they start.
- Wrap formulas in IFERROR where it makes sense — especially lookups and divisions — but only as a deliberate fallback, not to hide problems you have not diagnosed.
Auditing an entire workbook automatically
Doing all of this by hand is workable on a small sheet, but it gets slow and error-prone across a workbook with a dozen tabs and thousands of formulas — that is exactly where a stray #REF! goes unnoticed. If you would rather not click through every tab, you can audit the whole file at once with Sheet Audit Pack, a free Google Sheets add-on that scans the entire workbook, flags broken references and formula errors (among other checks), and lists every issue with its sheet and cell in an Audit Issues Log along with a risk score. It is the same process described above, done in one pass instead of tab by tab.
Frequently asked questions
How do I find all #REF! errors in Google Sheets at once?
Open Find and replace with Ctrl+H, type #REF!, enable Search within formulas, and set the scope to All sheets. For a more thorough sweep, add a helper column using ISERROR and filter it, or audit the whole workbook automatically so nothing hidden in an unscrolled area or another tab slips through.
Is it safe to wrap every formula in IFERROR?
No — IFERROR hides the error message, so if you apply it everywhere you can mask real problems like a broken lookup or a bad reference. Use it deliberately for expected cases (such as guarding a division or showing a clean fallback for an empty lookup), and only after you have confirmed the formula is otherwise correct.
What is the difference between #N/A and #REF!?
#N/A means a lookup ran correctly but found no match — the formula is intact, the data just is not there. #REF! means the formula itself points to something that no longer exists, usually because a row, column, or sheet was deleted. #N/A is a data issue; #REF! is a structural one.
