How to find errors in Excel formulas
Excel already has the tools. Turn on Show Formulas (Ctrl + `) to see every formula at once, run Formulas > Error Checking, use Go To Special to find typed numbers among formulas, and trace precedents on any total you doubt. The first six steps below work in every desktop version of Excel; the seventh needs the Inquire add-in.
Step by step
- Show every formula. Press Ctrl + ` (the key left of 1), or use Formulas > Show Formulas on any computer including a Mac. Scan each row: a cell that looks different from its neighbours is a warning. Press it again to switch back.
- Run Error Checking. Small green triangles in cell corners mark possible errors. Formulas > Error Checking walks through each one; choose Edit in Formula Bar to fix it or Ignore Error if it is deliberate.
- Find typed numbers. Home > Find & Select > Go To Special > Constants > Numbers selects every typed number. Inputs will be selected too - look for typed numbers inside rows that should be formulas, such as forecasts.
- Find error values. Go To Special > Formulas > Errors selects every cell showing an error value, including ones off screen. The table below explains each one.
- Trace a total. Select a total and use Formulas > Trace Precedents to see which cells feed it, or Trace Dependents to see what it feeds. Remove Arrows clears them. For circular references, use Formulas > Error Checking > Circular References.
- Step through a formula. Formulas > Evaluate Formula runs a long formula one part at a time, so you can see where the result goes wrong. The Watch Window keeps key totals in view while you change inputs.
- Compare whole sheets. If you have Microsoft 365 Apps for enterprise or Office Professional Plus, the Inquire add-in's Workbook Analysis and Compare Files list hidden sheets, links and changed formulas.
Excel error values: what they mean and the usual fix
| Error | Usual cause | Usual fix |
|---|---|---|
| #REF! | A formula points to a cell, row or sheet that was deleted | Rebuild the reference; check pasted-over rows |
| #DIV/0! | Dividing by zero or an empty cell | Check the divisor; show a blank only when the input is truly empty |
| #N/A | A lookup found no match | Check spelling, spaces and exact-match settings |
| #VALUE! | Text where a number is expected | Convert text numbers; remove stray spaces |
| #NAME? | Misspelt function or a name that does not exist | Fix the spelling or define the name |
| #NUM! | An impossible number, such as IRR that cannot be found | Check the inputs and signs of cash flows |
| #NULL! | A space instead of a comma or colon between ranges | Use , or : between ranges |
A warning about IFERROR
Wrapping formulas in IFERROR makes error values disappear, but the mistake is still there. Use it only where an error is expected (for example a lookup that may not match), never to tidy a model.
What these tools miss
Excel checks how formulas are written, not whether they make sense. It will not notice that interest uses last year's balance, that a growth rate is applied twice, or that a total includes a subtotal. For that, read the model with a list such as our financial model audit checklist.
A quick worked example
A sales row reads =C5*1.05, =D5*1.05, =E5*1.08. Show Formulas makes the 1.08 stand out. Error Checking may not flag it, because each cell is a valid formula. The fix: put the growth rate in one input cell per year and point every formula at it.
Questions
- Why does Error Checking show nothing when my numbers are wrong?
- It looks for patterns such as inconsistent formulas and error values. A formula that is valid but uses the wrong cell passes.
- Is the Inquire add-in in every Excel?
- No. Microsoft lists it for Office Professional Plus and Microsoft 365 Apps for enterprise. Other versions can still use the other six steps.
- What is the most common formula error in models?
- A row where one cell was overwritten or copied differently from the rest. Showing formulas finds it fastest.
Sources
- Microsoft Support: Detect errors in formulas
- Microsoft Support: What you can do with Spreadsheet Inquire
- Microsoft Support: Display the relationships between formulas and cells
See the Excel model check
Big model, little time? Send it and get a cell-by-cell list of issues.