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

The four silent bugs

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 four 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. 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. 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 all four in one click (the free option)

If you're going to do this weekly, manual auditing gets old. SheetBrain is a free Google Sheets™ add-on that runs all four checks deterministically (no AI calls, no quota) and returns a plain-English list of findings with proposed fixes.

The one-click version is in the sidebar: install the add-on, open the Health tab, and it auto-scans the active sheet. The formula-bar version:

Returns something like "6/10 — 14 issues found" in the cell. Pair it with:

To spill the full list of findings (cell, severity, category, suggested fix) as a table. Both are free forever, unlimited scans, no AI quota hit.

The add-on also detects schema drift — the one bug manual auditing can't catch — by quietly snapshotting your sheet's column layout each time you use Generate or Debug, then comparing 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.

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, or use a tool that snapshots column layout automatically.
• All four in one click: install SheetBrain and open the Health tab. Free forever, no AI quota used.

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