How to Find Broken Formulas in Excel
Broken formulas are one of the fastest ways to make an Excel workbook unreliable. A single formula error can flow into totals, dashboards, forecasts, and financial reports.
The good news is that Excel gives you several ways to find formula problems. The challenge is knowing what to look for and where errors may be hiding.
Common broken formula errors
The most common Excel formula errors include:
- #REF! — invalid or deleted reference
- #VALUE! — wrong type of value used in a formula
- #DIV/0! — division by zero or blank value
- #N/A — value not available, often from lookup formulas
- #NAME? — Excel does not recognize part of the formula
- #NUM! — invalid numeric calculation
- #NULL! — incorrect range intersection
- #SPILL! — dynamic array cannot spill properly.
Method 1: Use Go To Special
Excel has a built-in way to find formula errors.
- Press F5 or Ctrl + G.
- Click Special.
- Select Formulas.
- Tick Errors.
- Click OK.
Excel will select cells that contain formula errors.
Method 2: Use Find
You can also search for common error values.
Press Ctrl + F and search for:
- #REF!
- #VALUE!
- #DIV/0!
- #N/A
- #NAME?
This is simple but limited because you must search error types one by one.
Method 3: Check key output sheets first
Do not only scan raw data tabs. Start with the sheets that matter most:
- summary reports
- dashboards
- financial statements
- budget outputs
- client-facing sheets
- management reports.
If an error appears in a final output sheet, it is usually more urgent.
Method 4: Trace precedents and dependents
Use Excel’s Trace Precedents and Trace Dependents tools to understand where a formula gets its inputs and where its result flows.
This is useful when an error appears in a final report, but the cause is somewhere else.
Method 5: Check formulas after deleting rows or columns
Many broken formulas happen after someone deletes rows, columns, or sheets. This often creates #REF! errors.
Before sending a workbook, review formulas after structural changes.
Why broken formulas are risky
Broken formulas can:
- produce wrong totals
- break dashboards
- affect linked calculations
- confuse clients or managers
- reduce trust in your work.
Conclusion
Finding broken formulas manually is possible, but it becomes harder in large workbooks with many sheets and thousands of formulas.
SaferSheets scans workbooks for formula errors and highlights where they appear, why they matter, and what to check next.