How to Find Broken Formulas in Google Sheets (Free Tool Included)

Five bug classes that silently break decisions

After looking at hundreds of real Google Sheets files — finance models, ops trackers, client dashboards, hiring plans — almost every broken result comes from one of five causes:

1. Broken references — the formula is pointing at a cell, column, or sheet that no longer exists (or never did). Usually surfaces as #REF!, but sometimes silently returns 0 or "".
2. Mixed data types in a column — one cell contains 15000, the next contains "15,000", and the third contains "15K". Your eyes see three numbers. SUMIFS sees one number and two text strings.
3. Duplicate keys in a lookup column — VLOOKUP or XLOOKUP returns the first match. If the column has two rows with the same customer ID, one of them is being quietly ignored forever.
4. Formula pattern outliers — a copied formula column mostly follows the same pattern, but one row got overwritten, pasted, or customized without anyone noticing.
5. Schema drift — somebody renamed the "Amount" header to "Revenue" or moved Column F to Column H. Existing formulas still point at the old column letter, and now they're calculating the wrong thing against the wrong data.

Each one is individually easy to fix if you know it's there. The problem is you don't. That's the whole game.

1. Broken references

Broken references are the easiest to find because most of them leave a visible trace. The painful ones are the invisible subset.

Find them with Find & Replace

Open the Find dialog with Ctrl+F (or Cmd+F on Mac). Click the three-dots menu for advanced options, check "Also search within formulas," and search for:

• #REF! — the classic broken-reference marker Google Sheets inserts when a range is deleted
• #N/A — usually a missed lookup, occasionally a broken data connection
• #NAME? — typo in a function name or a reference to a deleted named range
• #VALUE! — a type mismatch (text where a number is expected)
• #DIV/0! — dividing by a cell that's now empty

Find broken VLOOKUP column indexes

This is the bug the Find dialog won't catch. If you have =VLOOKUP(A2, Products!A:C, 4, FALSE), the 4 means "return the fourth column." But the range only has three columns (A, B, C). Google Sheets returns #REF! — but only for the rows where a match is found. Rows without a match return #N/A, which looks like "lookup didn't find it" rather than "your formula is malformed."

There's no built-in way to find these in bulk. You either audit every VLOOKUP/XLOOKUP by hand, or you scan for them programmatically.

2. Mixed data types in a column

This is the bug that makes finance people quietly furious. You have a column of revenue figures. Most of them came from a CSV and are real numbers. A few were pasted from an email and are text strings that look like numbers. SUMIFS, COUNTIFS, and AVERAGEIFS will silently exclude the text ones. Your monthly total is 83% of what it should be. Nobody catches it because 83% still looks plausible.

The giveaway

Text values in a number column left-align by default. Actual numbers right-align. If you scroll a column of "numbers" and the alignment flickers row by row, you have mixed types.

Find them manually

In an empty cell next to your suspect column, use:

The result is the number of non-empty cells that aren't numbers. If it's zero, you're clean. If it's greater than zero, you have that many text-as-number cells in column F. You can also use ISNUMBER() across the range with conditional formatting to highlight the offenders in red.

3. Duplicate keys in a lookup column

VLOOKUP returns the first match. So does the typical XLOOKUP. If your "Customer ID" column has 500 unique IDs and 4 accidental duplicates, 4 lookups in the rest of the sheet are quietly returning the wrong row — forever. Every time the sheet updates, it's still wrong.

Find duplicates with COUNTIF

In a helper column next to your lookup key, use:

Any cell returning a value greater than 1 is a duplicate. Add conditional formatting to highlight rows where this is >1 and you'll see the duplicates immediately.

For a one-shot check of whether your key column is clean:

If the result is zero, every value is unique. If it's greater than zero, that's the number of duplicates you have.

4. Formula pattern outliers

Formula-filled columns usually repeat a pattern. In a revenue column, row 2 might calculate =B2*C2, row 3 calculates =B3*C3, row 4 calculates =B4*C4, and so on. One pasted-over row can break that pattern while still returning a plausible number.

Find them manually

Click into a formula-filled column and compare several adjacent formulas. If one row references a different row, a different column, uses a different operator, or has no formula where the nearby rows clearly do, inspect it. This gets tedious quickly, which is why Detector Set v0.4 flags conservative same-column outliers and missing copied formulas only when the local pattern is clear.

5. Schema drift (the worst one)

Your formulas reference columns by letter: SUM(F:F), VLOOKUP(A2, Products!A:D, 3, FALSE). Those letters are positional. When someone inserts a column before F, your F:F now points at whatever moved there. When someone renames "Amount" to "Revenue," the formula itself still works — but now you're summing Revenue when the template you've been trusting for six months was designed to sum Amount.

This is the hardest class of bug to detect manually because nothing looks broken. Every cell has a value. No red triangles. No #REF!s. The formula just returns the wrong number.

Manual approach

Keep a locked reference sheet with the original column order and headers. Compare it periodically. Most teams don't. That's why schema drift is the bug most likely to quietly make its way into a board deck.

Catching visible errors and silent Check issues in one click

If you're going to do this weekly, manual auditing gets old. SheetBrain is a free Google Sheets™ add-on whose Check tab runs Detector Set v0.4 — six deterministic detectors (broken references, visible formula errors, mixed data types, duplicate lookup keys, formula pattern outliers, missing copied formulas), no AI calls, no quota — and returns a plain-English list of findings with fix or review guidance where available. Schema drift after columns move or get renamed is handled separately by the Repair tab on the $9/mo Pro plan, since drift detection requires a captured schema snapshot rather than a static scan.

The one-click version is in the sidebar: install the add-on, open the Check tab, and click Scan to check the active sheet. The formula-bar version (pass the range as an unquoted reference; quoted strings are rejected):

Returns a 0–100 score string like "87 — 2 issues" in the cell. The score starts at 100 and drops 15 per error and 5 per warning, with a floor at 0; "100 — All clear ✓" means no findings. Pair it with:

To spill the full list of findings (cell, issue, severity, description) as a table. Both are free forever, unlimited scans, no AI quota hit. Tip: pass NOW() as a second argument — =SHEETBRAIN_HEALTH(A1:Z1000, NOW()) — to bypass Apps Script's argument-value memoization and force a refresh.

For schema drift — the one bug manual auditing can't reliably catch — SheetBrain's Repair tab quietly snapshots your sheet's column layout each time you use Generate or Debug, then compares against the current state when you click Repair. It shows you every formula that broke when a column moved, and proposes safe rewrites you approve one-by-one or in batch. Repair is AI-powered and lives on the $9/mo Pro plan; the deterministic Check covers visible formula errors and the other v0.4 Check issues for free.

When manual auditing is enough

You don't need a tool for every sheet. If a workbook has fewer than five formulas, no lookups, and one static contributor, the Ctrl+F for #REF! method plus eyeballing the column alignment is genuinely fine. The reason tools exist is that the moment a sheet is shared with two people, lives longer than a quarter, or feeds into a decision worth more than the tool — manual is no longer a real option.

TL;DR

• Broken references: Ctrl+F for #REF!, #N/A, #NAME? in formulas. Audit VLOOKUP column indexes manually.
• Mixed types: =COUNTA(range) - COUNT(range) tells you how many non-numeric cells are sneaking in.
• Duplicate lookup keys: =COUNTIF(col, cell) > 1 in a helper column; highlight with conditional formatting.
• Schema drift: keep a locked reference sheet of the original layout and compare periodically, or let SheetBrain's Repair tab ($9/mo Pro) snapshot the column layout automatically and propose safe rewrites when columns move or get renamed.
• Free one-click Check: install SheetBrain and open the Check tab. Detector Set v0.4 covers broken references, visible formula errors, mixed data types, duplicate lookup keys, formula pattern outliers, and missing copied formulas deterministically — no AI quota used.

Published 2026-04-19 · All posts · Product docs