Excel Formula Auditing: How to Find and Fix Broken Formulas

Finding All Errors

Ctrl+G → Special → Formulas → Errors

This selects all cells on the current sheet containing formula errors (#REF!, #NAME?, #VALUE!, #DIV/0!, etc.). But it only works on one sheet at a time.

To find errors workbook-wide, you'd need to repeat this on every sheet manually.

Understanding Error Types

• #REF! — Referenced cells were deleted
• #NAME? — Excel doesn't recognize a name in the formula
• #VALUE! — Wrong type of argument (text where number expected)
• #DIV/0! — Division by zero
• #N/A — Lookup value not found
• #NUM! — Invalid numeric value
• #NULL! — Incorrect range reference

Tracing Formula Dependencies

Trace Precedents (Alt+M+P): Shows arrows pointing from cells that feed into the selected cell. Helps understand where data comes from.

Trace Dependents (Alt+M+D): Shows arrows pointing to cells that use the selected cell. Helps understand the impact of changing a value.

Remove Arrows (Alt+M+A+A): Clears the trace arrows when done.

The Evaluate Formula Tool

Formulas tab → Evaluate Formula

This walks through a formula step by step, showing you exactly how Excel calculates each part. Essential for debugging complex nested formulas.

Error Checking

Formulas tab → Error Checking

Excel walks through each error on the sheet, offering explanations and fixes. But again, only works on one sheet at a time.

Finding Formula Inconsistencies

Sometimes formulas aren't broken — they're just inconsistent. A column of SUM formulas where one cell uses AVERAGE instead.

Ctrl+` (backtick) toggles formula view, letting you see all formulas as text. But comparing 1000 formulas visually is impractical.

Official Resources

• Detect errors in formulas — Microsoft's error detection guide
• Trace relationships — precedents and dependents