Excel Conditional Formatting: Highlight Errors in Cells

How-to basics · Updated November 19, 2026

Yes. In Excel for Microsoft 365, select the cells you want to check, then create a conditional-formatting rule using =ISERROR(A2), where A2 is the top-left cell in the selected range. Choose a format, such as a red fill, and Excel will highlight cells whose current values are errors.

What the rule does

Conditional formatting changes how a cell looks when a condition is true; it does not change the cell’s value or repair its formula. The ISERROR function tests whether a value is an Excel error and returns TRUE when it is. A conditional-formatting rule can use that result to apply a fill, font color, border, or other available format.

The rule responds to the cell’s current result. If a formula error disappears after its inputs are corrected, the highlight usually disappears too. This makes conditional formatting useful for spotting problems in changing reports, imported data, and calculation-heavy workbooks without having to scan every cell manually.

It catches actual error values, including common results such as #N/A, #VALUE!, #REF!, and #DIV/0!. It does not flag ordinary text that merely looks like an error. For example, a cell containing the text #N/A is not the same as a cell whose value is the #N/A error. That distinction matters when data has been imported from another system.

How to highlight errors with a formula rule

For example, if the cells to check are A2:A100, use A2 as the formula reference because it is the top-left cell of the selected range. The formula for that layout is =ISERROR(A2).

  1. Select the range to check, such as A2:A100. To check several columns, select the whole area, such as A2:D100.
  2. On the ribbon, choose Home > Conditional Formatting > New Rule.
  3. In the New Formatting Rule dialog, choose Use a formula to determine which cells to format.
  4. Enter the formula for the selected range, then choose Format and set the appearance you want, such as a light-red fill and dark-red font.
  5. Choose OK to close the Format Cells dialog, then choose OK again to create the rule.

The reference should normally be relative: A2, not $A$2. Relative references let Excel evaluate the rule separately for each cell in the selected area. When the rule applies to A2:A100, Excel effectively checks A2 for the first cell, A3 for the next, and so on. If you lock the reference as $A$2, every cell in the range will instead be judged by the value in A2, which is rarely what you want for an error-highlighting rule.

In Excel for Mac, the same formula-based approach is available through Home > Conditional Formatting > New Rule; dialog labels or layout can vary slightly by release. Excel for the web also supports conditional formatting, though its rule editor may look different from the desktop dialog. If a workbook is shared across versions, use a straightforward formula such as ISERROR and check that the formatting appears as expected in the versions your colleagues use.

Choosing the right range and reference

The selected range determines which cells can receive formatting, while the formula reference determines what Excel tests for each cell. Those are related but separate choices. If you select A2:D100 and use the relative reference A2, the rule checks each cell in that rectangular range individually. If the range begins at B5 instead, the formula should refer to B5. Matching the reference to the range’s top-left cell is the easiest way to avoid shifted or inconsistent highlighting.

Sometimes the value to test is in one column, but you want to highlight a whole row. Suppose the records occupy A2:D100 and the error status is in column D. To format each row according to the cell in D, select A2:D100 and use the formula =ISERROR($D2). Here the dollar sign fixes the column D, while the row number remains relative. Excel checks D2 for the first row, D3 for the next, and applies the format across the corresponding row. This is useful when a table has several fields but the error indicator is in one designated column.

For large or changing datasets, consider whether the selected range should include future rows. A rule applied only to A2:A100 will not automatically cover data entered in A101. You can extend the range later through Home > Conditional Formatting > Manage Rules, or apply the rule to a larger expected area. Avoid selecting an unnecessarily huge range if the workbook becomes sluggish or difficult to manage; a sensible working area or an Excel table can make rules easier to maintain.

Highlighting only certain error types

ISERROR is the broad choice: it catches all error values rather than limiting the rule to one particular error. That is often desirable for a general quality check, since an unexpected #REF! or #VALUE! should not be missed just because the rule was designed around one known issue.

If the specific concern is a missing lookup result, you can highlight only #N/A errors. For a selected range beginning at A2, the formula is =ISNA(A2). This is more targeted than ISERROR. It will not highlight other errors, so it is not a substitute for a general error scan if other calculation failures matter.

Excel also provides the ISERR function, which detects errors except #N/A. For a range beginning at A2, the formula is =ISERR(A2). Use it only when the distinction is useful: if #N/A is an expected or separately handled result, ISERR can flag other error types without marking that one. For most readers who simply want to see every calculation problem, ISERROR is the clearer and safer starting point.

Common problems and how to fix them

  • The wrong cells are highlighted: Check the top-left cell of the selected range against the formula reference. Also check whether dollar signs have fixed a row or column unintentionally.
  • Every cell in the range gets the same result: A fully locked reference such as $A$2 may be making Excel test one cell for the whole range. Use a relative reference for cell-by-cell checks.
  • Nothing is highlighted: Confirm that the cells contain actual Excel errors, not text that resembles one. Open Home > Conditional Formatting > Manage Rules and verify the rule formula and the Applies to range.
  • The highlight is hidden: Another conditional-formatting rule may be applying a competing format. In Manage Rules, inspect the rules that apply to the same cells and their order. If an earlier rule has Stop If True selected, later rules may not be evaluated for cells where that earlier rule is true.
  • The rule worked, then stopped covering new data: Check whether the new rows fall inside the rule’s Applies to range. Expand the range or arrange for future records to be included.

It can also help to test the formula in an unused worksheet cell before adding the rule. For a cell A2 that currently contains an error, the formula =ISERROR(A2) returns TRUE; if A2 contains a normal number or text value, it returns FALSE. This quick check separates a formula-reference problem from a formatting or range problem.

Conditional formatting versus other ways to find errors

Conditional formatting is best when you want errors to remain visible in context as the sheet changes. It is not the only option. Excel’s error indicators and formula-auditing tools can help investigate individual cells, while Find or filters can help locate specific displayed content in some situations. Those approaches answer different questions: formatting marks cells visually, whereas auditing tools help trace how a formula reached its result.

You can also use a formula in a helper column if you need a searchable TRUE/FALSE flag. With the value to check in A2, the helper formula is =ISERROR(A2). Copy it down to test other rows. A helper column may be more useful when you want to filter records, count errors, or build a summary; conditional formatting is less intrusive when the goal is simply to draw attention to the affected cells. Both methods can be used together.

Do not confuse highlighting with suppressing an error. A conditional-formatting rule leaves the error intact, which preserves the information needed to diagnose it. Replacing errors with blank text or a custom message is a separate formula decision and can hide a problem from someone who relies on the workbook. If an error is expected, such as a lookup that legitimately has no match, decide whether it should be handled in the calculation or merely identified visually.

Maintaining rules in a shared workbook

When a workbook has several conditional-formatting rules, use Home > Conditional Formatting > Manage Rules to review which ranges they cover, edit formulas, or remove obsolete rules. The dialog lets you inspect rules for the current selection or the worksheet, depending on the option shown. Descriptive rule ranges and a consistent format help other people understand why a cell is colored.

Keep the visual signal accessible and unambiguous. A red fill is common, but color alone may be hard to distinguish for some readers or in print. Pair it with a clear font color or border where appropriate, and avoid using the same appearance for unrelated conditions. Conditional formatting is a visual aid, not an explanation of the cause; a note, status column, or documented process may be needed in a workbook where users must act on the error.

Frequently Asked Questions

Can Excel highlight every error in a worksheet?
Yes. Select the area you want checked and apply an ISERROR formula rule using the range’s top-left cell as the reference. To check more than one area, create rules for each area or select a suitable combined range.

Does conditional formatting fix the error?
No. It changes the appearance of the cell, not the formula or its value. Correct the underlying formula or input separately if the error is not expected.

Will ISERROR highlight a cell containing the text #N/A?
No. It detects an actual Excel error value. Text that happens to display the same characters is ordinary text and does not make ISERROR return TRUE.

How do I highlight only #N/A errors?
Use an ISNA formula rule. If the selected range starts at A2, enter =ISNA(A2). It will identify #N/A values, but not other types of errors.

Why does the rule highlight the wrong row?
The formula may not match the selected range’s top-left cell, or its row and column references may be locked incorrectly. Check the Applies to range and use relative references for cell-by-cell evaluation.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

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

Spotted an error in this guide? Let us know.