How to Find Errors in Excel Spreadsheets

Updated 2026-06-03

Quick answer

To find errors in Excel, use Formulas → Error Checking to flag #REF!, #DIV/0!, #VALUE! and inconsistent formulas, and Show Formulas (Ctrl+`) to scan logic. These catch visible errors; the costly ones in financial models are logical: a wrong reference that still returns a number. Formula Audit XL's consistency check highlights formulas that break a pattern, exposing errors that don't throw an error code.

Errors in Excel come in two distinct categories: visible errors (cells displaying #REF!, #DIV/0!, #VALUE! and similar error codes) and logical errors (formulas that return a number but the wrong one). Both categories damage financial models, but logical errors are far more dangerous because they are invisible without active investigation. This guide covers how to find both.

Visible errors vs logical errors

Visible errors announce themselves. When a formula breaks, Excel shows a clear indicator:

#REF!: the formula references a deleted or invalid cell.
#DIV/0!: division by zero or by an empty cell.
#VALUE!: wrong data type (e.g. text where a number is expected).
#NAME?: unrecognised function name or formula text.
#N/A: a lookup function (VLOOKUP, INDEX/MATCH) found no match.
#NUM!: invalid numeric operation (e.g. square root of a negative number).
#NULL!: incorrect range operator in a formula.

Logical errors return a number that looks plausible but is wrong. =C5/B5 instead of =D5/B5; =SUM(C5:C9) instead of =SUM(C5:C10) (omitting the last row); =B14*12 when the model is already in annual figures. These do not trigger any Excel warning.

In a financial model, logical errors are the costlier category. A #REF! error is visible in the output and will be caught in review. An off-by-one range in a sum formula may go undetected through multiple review rounds.

Error Checking tool

Formulas → Error Checking (or Alt + M, K) cycles through every cell containing a visible error on the active sheet:

The dialog shows the error and suggests possible causes.
Edit in Formula Bar: fix the formula directly.
Ignore Error: marks this error as reviewed (suppresses the green triangle warning).
Next / Previous: moves to the next error.

For a clean new model with no errors, the dialog will say “The error check is complete for the entire sheet.” If it finds errors, work through each one before proceeding.

The dropdown next to Error Checking also includes Circular References. See How to Find Circular References in Excel for the full workflow.

Common error codes and how to fix them

Error	Likely cause	Fix
`#REF!`	Referenced cells deleted or formula moved	Trace Precedents to identify the broken reference; update to the correct cell
`#DIV/0!`	Denominator is zero or empty	Wrap with `IFERROR(formula, "")` or `IF(B5=0, 0, A5/B5)`
`#VALUE!`	Text in a numeric calculation	Find the text cell (often a paste issue) and convert to number
`#NAME?`	Mistyped function name or undefined named range	Check spelling; open Name Manager to verify named ranges exist
`#N/A`	Lookup returned no match	Add `IFERROR()` wrapper or verify the lookup value exists in the source range
`#NUM!`	Invalid numeric operation	Check inputs: negative under a square root, or RATE/IRR not converging
`#NULL!`	Space instead of `:` or `,` in a range	Correct the range operator in the formula

Finding errors across multiple sheets

Error Checking runs on one sheet at a time. To find errors across all sheets:

Method 1: Find and Replace:

Press Ctrl + F.
In the Find What box, type #.
Set Look in to Values.
Click Find All: Excel lists every cell containing an error string.
Click any result in the list to navigate there.

Method 2: Error Checking per sheet: right-click any sheet tab → Select All Sheets, then run Error Checking. Note: this groups sheets for viewing but Error Checking still processes one sheet at a time.

Why models fail silently (logical errors)

The errors that cause real damage in financial models are not error codes. They are wrong numbers that pass visual inspection. Common sources:

Range not extended. A SUM formula covers C5:C9 but the model was expanded and the correct range is C5:C10. Excel has no way to flag this.
Wrong row reference. In a table of years, one column picks up a value from the wrong year due to a copied formula that was not anchored correctly.
Hardcoded assumption. =A1*1.08 where 1.08 is an undocumented growth rate. If the assumption changes, only the cells where it is hard-coded update; other cells using a named input cell do not.
Inconsistent formula. Row 14 uses =B14-C14 but every other row in the section uses =B14+C14. The sign change is wrong but produces a number.

Catching logical errors with Formula Audit XL

Formula Audit XL‘s consistency check colours every cell in the model by formula pattern, making cells that deviate from the pattern of their neighbours immediately visible. No reading of individual formula text required. This is how logical errors and inconsistent formulas surface in seconds across an entire model rather than during manual cell-by-cell review.

Common pitfalls

Using IFERROR to hide errors instead of fixing them. Wrapping a broken formula in IFERROR(broken_formula, 0) makes the error invisible and the cell returns zero or blank, which propagates into sums and calculations. Fix the underlying error rather than masking it.
Ignoring #N/A in lookup tables. IFERROR wrappers on lookups are sometimes legitimate (when no match is expected). But if a lookup is failing when it should succeed, the #N/A is informative. Investigate before suppressing.
Treating “no errors” as “model is correct.” Passing Error Checking with zero errors means the sheet has no visible error codes. It says nothing about logical correctness. A formal audit requires tracing key outputs.

The faster way

Run this check across your entire model with Formula Audit XL.

Audit Checks

Frequently asked questions

What does #REF! mean in Excel?

#REF! means a formula contains an invalid cell reference, typically because the cells it referenced have been deleted or the formula was moved to a location that makes the reference invalid. Trace Precedents on the cell and re-enter the reference to the correct cell.

What does #DIV/0! mean?

#DIV/0! means a formula is attempting to divide by zero, either by referencing an empty cell or a cell containing zero as the denominator. Wrap the formula in an IFERROR or IF check: =IF(B5=0, "", A5/B5).

What does #VALUE! mean?

#VALUE! means a formula is performing an operation on the wrong data type. For example, trying to multiply a cell containing text by a number. Check whether cells that should contain numbers actually contain text (common when data is pasted from external sources).

How do I find all errors across the whole workbook at once?

Use Ctrl + F (Find), set 'Look in' to Values, and search for '#'. This finds any cell displaying an error value across the active sheet. Repeat for each sheet, or use a dedicated audit tool that searches the whole workbook simultaneously.

What is a logical error in a financial model?

A logical error is a formula that calculates correctly (no error code) but produces the wrong result because it references the wrong cell, uses the wrong operator, or applies the wrong logic. For example, =C5/B5 instead of =D5/B5 returns a number without error but may be completely wrong. Logical errors are harder to find than visible errors. They require tracing and verification, not just error code scanning.

Related guides