How to Find Broken Formulas in Excel

Broken formulas come in two flavors: the loud ones that throw #REF! or #DIV/0!, and the quiet ones that return a plausible wrong number. Excel can find the loud ones quickly. The quiet ones take more deliberate hunting.

Find error values with Go To Special

Error values are the easiest broken formulas to catch because Excel can select them all at once.

1. Press Ctrl+G, then click Special.
2. Choose Formulas, then check only the Errors box and click OK.
3. Every cell returning an error is now selected, so you can fix them in one pass.
4. Press Tab to move through the selected cells one at a time.

Understand what each error means

#REF! means a referenced cell was deleted. #DIV/0! means a divisor is zero or blank. #VALUE! means a type mismatch, often text where a number is expected. #NAME? means a misspelled function or an undefined name.

#N/A from a lookup usually means the key is not in the table. Knowing the cause from the error code tells you where to look without tracing every input.

Catch silent errors that return wrong numbers

The dangerous breaks return a number, not an error. A SUM that stops one row short, a lookup pointing at the wrong column, or a reference that drifted during a drag-fill all produce believable garbage.

Use Error Checking on the Formulas tab to flag inconsistent formulas in a row or column, where one cell differs from its neighbors. This catches the drag-fill that missed a cell.

Find the hidden hardcode that overrides a formula

A common silent break is a cell where someone typed a number over what used to be a formula, breaking the chain at that point. Everything below it stays formula-driven, so the sheet looks intact.

ModelMint's Find Hardcodes scans for numbers sitting inside formulas and standalone constants in calculation areas, surfacing exactly these overrides. It is faster than toggling formula view and reading every cell.

Trace a wrong result back to its source

When a cell returns the wrong number with no error, trace its precedents to find where the value goes off. Walk back layer by layer until a number stops matching what you expect.

ModelMint's Formula Trace makes this quick: step through precedents with the arrow keys and watch each source cell highlight, so you land on the broken link without reading the whole sheet.