Highlight Cells That Contain Errors

Excel Formulas › Conditional Formatting

All versionsISERROR

Errors hide in big sheets until they break a total. An ISERROR conditional-formatting rule lights up every #N/A, #DIV/0!, or #VALUE! so you can fix them before they spread.

Quick formula: select your range, then add a formula CF rule:

=ISERROR(A1)

Any cell evaluating to an error returns TRUE and gets the format — an instant visual audit of the whole sheet.

Functions used (tap for the full reference guide):

ISERROR ERROR.TYPE

The example

A calculation column where two cells error out.

	A	B
1	Input	Result
2	10	5
3	0	#DIV/0!
4	abc	#VALUE!

The formula

The conditional-formatting rule:

=ISERROR(A1) // TRUE for any error value

How it works

ISERROR tests whether a cell evaluates to an error:

Select the range you want to audit, then add a formula rule: =ISERROR(A1) (match the relative reference to your active cell).
ISERROR returns TRUE for every error type — #N/A, #DIV/0!, #VALUE!, #REF!, #NAME?, #NUM!, #NULL!.
Pick a bold fill (red works well) so errors jump out, and click OK.
To exclude the often-expected #N/A, use ISERR instead — it ignores #N/A and flags the rest.

Fix vs flag: highlighting finds errors; to suppress them in the result use IFERROR(formula, ""). Many pros keep a CF error rule on and wrap formulas in IFERROR — the rule catches anything that slips through.

Try it: interactive demo

Live demo

Divide by the number; zero or text errors highlight.

Divisor

Variations

Ignore #N/A (flag the rest)

ISERR skips #N/A:

=ISERR(A1)

Only #N/A

Catch lookups that found nothing:

=ISNA(A1)

Suppress instead of flag

Hide the error in the formula:

=IFERROR(yourFormula, "")

Pitfalls & errors

ISERROR vs ISERR vs ISNA. ISERROR catches everything; ISERR excludes #N/A; ISNA catches only #N/A. Pick the one that matches what’s “expected.”

Match the relative reference. The rule’s cell (A1) must line up with the active cell of the selection, or it tests the wrong cells.

Highlighting doesn’t fix. The rule only colors errors — you still need to correct the underlying formula or wrap it in IFERROR.

Practice workbook

📊

Download the free Highlight Cells That Contain Errors practice workbook

An audit sheet with the ISERROR rule applied, the ISERR/ISNA variants, and IFERROR suppression, plus 4 challenges with answers. No sign-up required.

Frequently asked questions

How do I highlight cells with errors in Excel?

Select the range and add a formula rule =ISERROR(A1) with a bold fill. Every error value (#N/A, #DIV/0!, #VALUE!, etc.) gets highlighted.

How do I highlight errors but ignore #N/A?

Use =ISERR(A1) instead — it flags all error types except #N/A, which is often expected from lookups.

How do I just hide the errors?

Wrap the formula in IFERROR, e.g. =IFERROR(yourFormula, ""), to show a blank instead of the error.

Stop fighting formulas. Learn them in a day.

This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.

See the Formulas & Functions Class

Function references: ISERROR · ERROR.TYPE