Conditional Formatting Excel Error Values: How to Flag

Errors & fixes · Updated November 19, 2026

To highlight Excel error values, select the cells, go to Home > Conditional Formatting > New Rule > Format only cells that contain, choose Errors, and set a format. For a formula-based rule, use =ISERROR(A2) when A2 is the top-left cell in the selected range; Excel will then highlight cells containing errors.

What Excel error values mean

An error value appears when Excel cannot complete a calculation or retrieve a requested result. Common examples include #DIV/0! for division by zero, #N/A when a value is unavailable, #VALUE! for an argument of the wrong type, #REF! for an invalid reference, #NAME? for text Excel does not recognize as a function or name, and #NUM! for an invalid numeric calculation. #NULL! can appear when a formula uses an incorrect range intersection.

Newer Excel features can also produce errors such as #SPILL! when a dynamic-array result cannot expand into its intended cells, or #CALC! for certain calculation problems. Conditional formatting changes how the cell looks; it does not fix the calculation, remove the error, or change the value used by other formulas. That distinction is useful: a bright fill can make a problem easier to find while preserving the underlying information for diagnosis.

Apply the built-in Errors rule

For a straightforward range, Excel has a built-in condition for error cells. It is often the quickest choice when you want one style for every kind of error and do not need to exclude particular errors.

  1. Select the cells to check, such as a column of formula results or a rectangular report area.
  2. On the Home tab, choose Conditional Formatting > New Rule.
  3. Choose Format only cells that contain.
  4. In the first condition list, choose Errors. The dialog wording and layout can vary slightly by Excel build.
  5. Choose Format, select a fill, font, or border, and confirm the dialogs.

To alter an existing rule, select a cell in the range and choose Home > Conditional Formatting > Manage Rules. In Show formatting rules for, choose the relevant worksheet if needed. Check the Applies to range so that it covers the intended cells, then edit or delete the rule. On Excel for Mac, the Conditional Formatting command is also on the Home tab, though some dialog details may be arranged differently. Excel for the web supports conditional formatting, but editing options can differ from the desktop app; use desktop Excel if a needed rule option is unavailable.

Use ISERROR for a formula-based rule

A formula rule is useful when you want to be explicit about the test, combine error detection with another condition, or format an entire row based on an error in one column. ISERROR returns TRUE for an error value and FALSE for a non-error value. The built-in test is simpler for basic highlighting; a formula gives you more control.

For example, with formula results in A2:A100 and A2 as the active top-left cell when you create the rule, use this formula and set Applies to to A2:A100:

=ISERROR(A2)

To create it, select the range, choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format, enter the formula, and choose a format. The reference A2 is relative: Excel adjusts it for each cell in the Applies to range, testing A3 in the next row and so on. If the first cell in your selected range is D5, write the formula using D5 instead. A reference mismatch is a common reason a rule highlights the wrong cells.

Choose which errors to highlight

ISERROR catches all error values, including #N/A. Sometimes that is too broad. For instance, a lookup formula may intentionally return #N/A while a source list is incomplete, and you might want to distinguish that case from a broken reference or invalid calculation.

  • All errors: Use the built-in Errors condition or ISERROR.
  • Errors other than #N/A: Use ISERR, which tests for errors except #N/A.
  • Only #N/A: Use ISNA, which tests specifically for the not-available error.

With results in A2:A100 and A2 as the first cell of the range, an ISERR formula rule is:

=ISERR(A2)

With the same layout, a rule that targets only #N/A is:

=ISNA(A2)

These tests let you assign different formats to different classes of errors, such as a red fill for calculation problems and an amber fill for expected missing lookup results. They do not identify the cause of an error; they only test the cell value.

Highlight a whole row when a cell has an error

Sometimes the useful visual cue belongs on the entire record, not just the cell containing the problem. Suppose a table occupies A2:F100 and column C contains a formula that should be checked. Select A2:F100, create a formula-based rule, and use this formula:

=ISERROR($C2)

The dollar sign locks the column C reference while leaving the row number relative. Consequently, cells across row 2 all test C2, and cells across row 3 test C3. Set Applies to to A2:F100. If you omit the dollar sign before C, Excel shifts the tested column as the formatting moves across the row, which can produce unexpected results.

If the row should be highlighted when any of several selected columns contains an error, with the table in A2:F100 and columns C, D, and E to test, use:

=OR(ISERROR($C2),ISERROR($D2),ISERROR($E2))

Apply this rule to A2:F100. OR returns TRUE if at least one of the three tests finds an error. For a very wide table, it may be simpler to highlight the individual result cells or create a helper column that summarizes the row, rather than writing a long formula listing many columns.

Format, scope, and rule priority

Choose a format that calls attention to errors without making the sheet hard to read. A pale fill plus a dark font is often clearer than a saturated fill, especially in a dense report. A border or bold font can help when cell fills already carry meaning. Conditional formatting is a visual layer, so a color alone should not be the only way a critical issue is communicated; readers may print in grayscale or use accessibility tools.

Check the Applies to box carefully. If the rule covers only A2:A20, errors below row 20 will not be highlighted. If it covers an unintended block, unrelated cells may be formatted. When several rules affect the same cells, their order can influence the result. In Manage Rules, review the rule priority and the Stop If True setting where available. A higher-priority rule or a rule that stops evaluation can prevent a later format from appearing as expected. If the fill looks wrong, temporarily simplify overlapping rules to identify the conflict.

Common problems and how to diagnose them

  • The rule highlights the wrong cells: Confirm that the formula refers to the top-left cell of the Applies to range. Check whether row or column references need dollar signs.
  • Errors are not highlighted: Confirm the cells contain actual Excel errors, not text that merely looks like #N/A. A cell containing the characters #N/A as text is not the same as the #N/A error value.
  • Some errors are missed: Check whether the rule uses ISERR, which deliberately excludes #N/A, instead of ISERROR. Also inspect the Applies to range for gaps.
  • The error disappeared: A formula using IFERROR may replace the error with a value or blank. Conditional formatting sees the replacement, not the original error, so an error test will be FALSE.
  • The rule appears inconsistent: Review other conditional-formatting rules on the same cells, their priority, and any Stop If True option.

For text that looks like an error, use a text-specific rule rather than ISERROR. Conversely, if a formula returns a real error, a text search is not the right test. When troubleshooting, click the cell and inspect the formula bar and formula itself; the displayed appearance alone does not tell you whether the contents are an error value or text.

Conditional formatting versus fixing or hiding errors

Highlighting is appropriate when errors need attention but should remain visible, such as during a data review or while formulas are being audited. It does not repair the formula. To fix an error, trace the formula’s inputs and references, check whether a divisor can be zero, verify lookup values, and inspect any spilled-array destination that might be blocked.

IFERROR is different: it returns a chosen alternative when its first argument evaluates to an error. That can make a report cleaner, but it can also conceal an unexpected problem if used without thought. If a formula has already converted an error to a dash, zero, or blank, conditional formatting cannot detect the original error from that cell. Consider keeping a visible diagnostic column or applying error handling only where a missing result is an expected outcome.

Frequently Asked Questions

Can conditional formatting highlight all Excel errors automatically?
Yes. Select the range and use Home > Conditional Formatting > New Rule > Format only cells that contain, then choose Errors and a format. A formula rule using ISERROR is another option.

Does ISERROR include #N/A?
Yes. ISERROR returns TRUE for #N/A as well as other error values. Use ISERR if you want to exclude #N/A, or ISNA if you want to target only #N/A.

Can I highlight a row instead of just the error cell?
Yes. Apply a formula rule to the whole row range and lock the column being tested, while leaving the row reference relative. For example, if column C is checked in a range beginning at A2, use =ISERROR($C2).

Will conditional formatting find an error written as text?
No. Text that looks like #DIV/0! is not necessarily an Excel error value. ISERROR tests actual errors, not matching text; use a text-based condition if the cell contains characters rather than an error result.

Why does an error disappear from the highlighted range?
The formula may use IFERROR to return a substitute such as zero, a dash, or a blank. The cell then contains the substitute rather than an error, so an error-formatting rule has nothing to detect. Inspect the formula or keep a separate check for the underlying issue.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Errors & fixes

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.