Find Broken Excel Formulas Automatically

What Is a Broken Excel Formula?

A broken formula is any formula that no longer does what it was originally meant to do. That usually shows up in one of two ways:

• A visible error — something like #DIV/0! (dividing by zero, often an empty cell), #NAME? (a typo in a function name or a broken named range), or #REF! (a formula pointing to a cell or sheet that no longer exists).
• A silent overwrite — someone types a number directly into a cell that used to hold a formula. There's no error message at all. The cell just quietly stops updating, and everything downstream that depends on it is now wrong too, with no warning.

The second kind is the more dangerous one, precisely because Excel gives you no indication anything changed.

How to Find Broken Formulas Manually

A few ways people check for this by hand:

• Press Ctrl + ` (grave accent) to toggle "Show Formulas" mode, which displays the actual formula in every cell instead of its result — useful for eyeballing a small sheet.
• Use Go To Special → Formulas (or Ctrl+G, then Special) to highlight every cell that currently contains a formula, which by elimination shows you which cells in a normally-formula-filled column don't.
• Use Trace Precedents and Trace Dependents (Formulas tab) on a specific cell to visually see what it depends on and what depends on it.

These work fine for a handful of cells. They get genuinely difficult the moment a workbook has real size to it.

Why This Gets Hard in Large Workbooks

"Show Formulas" mode dumps every formula on screen at once — readable for 20 cells, unusable for 2,000. Go To Special tells you which cells have formulas, but not whether a formula's actual logic is still correct. And nothing in Excel proactively tells you "hey, this cell used to be a formula and now it's just a number" — you have to already suspect a specific cell to go check it.

In practice, the formulas that cause real damage are rarely the ones with a visible error. They're the ones sitting quietly among a column of otherwise-consistent formulas, looking completely normal, until someone downstream notices a number doesn't add up.

How Sheet Audit Checks Your Formulas

Once you upload a workbook, Sheet Audit runs a fixed set of deterministic checks against every cell, on every sheet — the same checks, every time, regardless of file size:

• Formula errors — every #DIV/0!, #REF!, #NAME?, and similar error value, flagged with the exact cell address.
• Pattern breaks — the specific, common case of a hardcoded number sitting in a column where the cells above and below it are still real formulas, a strong signal a formula was accidentally overwritten.
• Pivot-aware detection — real pivot table output is recognized and never mistaken for a broken formula pattern.
• Critical assumption tracing — which cells the rest of your model actually depends on, so you know what to double-check first.

Deterministic code finds the specific issue; a written summary then explains what it means in plain language — not the other way around.

Example

Frequently Asked Questions