Conditional Formatting for Errors in Excel: How to Do It

How-to basics · Updated November 19, 2026

To highlight error cells in Excel, select the range, choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format, and enter a formula such as =ISERROR(A2) when A2 is the top-left cell of the selected range. Choose a format, then select OK; Excel will apply it to cells whose values are errors.

How error highlighting works

Conditional formatting changes a cell’s appearance when a rule evaluates as true. It does not remove, repair, or change the underlying error. The cell still contains its error value, and formulas that refer to it continue to behave as they did before. The formatting is a visual signal that helps you find issues in a large sheet.

Excel’s ISERROR function returns TRUE when its argument evaluates to any error value and FALSE otherwise. That makes it a useful test for a conditional formatting rule. It can flag common errors such as #DIV/0!, #VALUE!, #REF!, #NAME?, #NUM!, and #N/A, as well as other Excel error values. It does not flag ordinary text that merely looks like an error, such as a text entry containing the characters #N/A.

There is no need to build a separate rule for each error type if your aim is to highlight every error. A single ISERROR-based rule is usually easier to maintain. You can choose a fill color, font color, border, or combination that stands out without making the sheet difficult to read.

Set up a rule in Excel for Microsoft 365

On Excel for Microsoft 365 for Windows, use these steps:

  1. Select the cells to check. For example, select A2:A100 if that column contains calculated results.
  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 that tests the top-left cell of the selected range.
  5. Select Format, set the fill, font, border, or other available formatting, and choose OK.
  6. Choose OK again to save the rule. If needed, open Home > Conditional Formatting > Manage Rules to inspect its range and priority.

For example, if the selected range is A2:A100, the rule formula is =ISERROR(A2). Excel evaluates the rule separately for each cell in the range: it tests A2 for the first cell, A3 for the next, and so on. The reference is relative, so it adjusts as the rule is applied down the column.

If you select a range that starts somewhere else, use that range’s top-left cell in the formula. For example, if the selection starts at C5, test C5 rather than A1. The formula is not a calculation you enter into the worksheet; it belongs in the conditional formatting rule dialog.

Choose the right cell reference

Reference style determines what Excel checks as it applies a rule. When you want each cell to be tested individually, use a relative reference to the selected range’s top-left cell. For a range such as B2:D20, that means using B2. Each cell is then checked in turn, rather than every cell checking B2.

Sometimes the goal is to color an entire row when one particular column contains an error. Suppose rows 2 through 100 contain records and column D holds a status formula. Select A2:D100, then use this rule formula:

With status formulas in D2:D100 and the full record range A2:D100 selected, use =ISERROR($D2). The dollar sign fixes the test to column D while the row number remains relative. As the rule moves down the table, it checks D2 for row 2, D3 for row 3, and so on; an error in the status cell highlights the corresponding row.

A common setup mistake is to lock both the column and row, as in =ISERROR($D$2), when the intention is to check each record’s own status cell. That would make every row depend on D2. Use dollar signs only where you want a reference to stay fixed. You can also select a rule in Manage Rules and check its Applies to range if the formatting seems to shift unexpectedly.

Target one kind of error instead of all errors

If only one error matters, use a more specific test. For example, ISNA identifies #N/A values without also highlighting #DIV/0! or #VALUE!. This is useful in lookup reports where a missing match is expected to be investigated, while other error types need a separate treatment.

With lookup results in C2:C200, select C2:C200 and use =ISNA(C2) as the rule formula. The rule highlights cells in that range whose value is #N/A, but not cells with other errors. ISERR is another option: it detects errors other than #N/A. Choose ISERROR when every error should be visible; choose a narrower test only when distinguishing error types helps the reader act on them.

For a rule that compares directly with a particular error, a formula can also use an error-checking function rather than a text comparison. Comparing the cell to the text string “#N/A” is not equivalent: a true Excel error and text that looks like an error are different values. In most spreadsheets, ISNA is the clearest way to target the actual #N/A error.

What conditional formatting does not do

Highlighting is not the same as error handling. It does not stop an error from appearing, make a dependent formula return a normal value, or correct the original cause. For instance, a #DIV/0! result may mean a formula divided by zero or by a blank cell; the color can draw attention to it, but the formula or its input still needs review.

If you want to show a message or an alternate result instead of an error, a worksheet formula may be appropriate. With a calculation in A2, enter =IFERROR(A2,"Check input") in another cell to display the text Check input whenever A2 returns an error. This changes the displayed result in the formula cell; it does not merely apply formatting, and it can hide useful diagnostic information if used indiscriminately. Consider whether users need to see the original error before replacing it.

Conditional formatting is often preferable when the error itself should remain available for troubleshooting. A common compromise is to leave the calculation untouched, highlight errors, and add a separate explanatory column or note for users who need guidance.

Common problems and how to fix them

  • Nothing is highlighted: Confirm that the selected cells actually contain Excel error values, not text that resembles one. Check the formula and the Applies to range in Manage Rules.
  • The wrong cells are highlighted: Match the formula’s first reference to the top-left cell of the Applies to range. Check for dollar signs that may be locking a row or column unintentionally.
  • Every cell gets the same formatting: The formula may be referring to one fixed cell, such as =ISERROR($A$2). Use a relative row or column where the test needs to move with each cell.
  • The format appears inconsistent: More than one conditional formatting rule may apply to the same cells. In Manage Rules, review the rules and their order. A higher-priority rule with Stop If True enabled can prevent lower-priority rules from affecting the cell.
  • The fill color is hard to see: Choose a format that contrasts with the worksheet’s existing colors. A pale fill with dark text is often easier to read than a saturated fill, especially when the range contains many results.

After making changes to a rule, verify it on a known error cell and a normal cell. This simple check can catch an incorrect range or reference before the workbook is shared. If errors appear only after recalculation, allow Excel to finish calculating and check the rule again.

Rules, priority, and keeping the workbook manageable

Conditional formatting rules can overlap. For example, one rule might color negative numbers red while another highlights errors. Excel evaluates applicable rules according to their priority in the Conditional Formatting Rules Manager. If two rules set the same formatting property, priority can determine which appearance is shown. Review the order when a cell’s formatting does not match what you expect.

For a large workbook, avoid creating many nearly identical rules for individual cells. Apply one rule to a coherent range, and use references that adjust correctly across it. If a table grows, check whether the rule’s Applies to range includes new rows. You can edit or remove a rule through Home > Conditional Formatting > Manage Rules; choose the relevant worksheet from the display options if necessary.

Conditional formatting is recalculated as cell values change, so an error that is corrected should no longer meet an ISERROR rule. If the old color remains, inspect whether another rule, a manually applied format, or an incorrect Applies to range is responsible. Manual formatting is separate from conditional formatting and may remain when a rule no longer applies.

Excel on Mac and Excel for the web

In Excel for Microsoft 365 on Mac, the same formula-based approach is available. Select the range and use the Home tab’s Conditional Formatting controls to create a new rule, then choose the option to use a formula and set the format. Dialog wording or layout can differ slightly by release, but the ISERROR formula and relative-reference logic are the same.

Excel for the web also supports conditional formatting, though the available controls and dialog layout can differ from the Windows desktop app. If a workbook’s rule is difficult to edit in the browser, open it in the desktop application and use Home > Conditional Formatting > Manage Rules. A rule created with an ordinary ISERROR formula is a standard worksheet feature, not a macro.

Frequently Asked Questions

Can I highlight all errors in a selected range?
Yes. Create a formula-based rule with ISERROR and refer to the top-left cell of the range. Apply the rule to the entire range so Excel checks each cell as it evaluates the formatting.

Does conditional formatting fix the error?
No. It changes the cell’s appearance only. The error remains in the cell, so you can still inspect its cause and any formulas that depend on it.

Can I highlight only #N/A errors?
Yes. Use ISNA in the rule formula instead of ISERROR. ISNA targets #N/A, while ISERROR also detects other error values.

Why does Excel highlight cells containing the text #N/A?
A cell may contain the literal text “#N/A” rather than the actual #N/A error. ISERROR and ISNA test error values, not ordinary text. If you also need to format text entries, make a separate rule that checks for that text.

Can I highlight a whole row when one cell contains an error?
Yes. Apply the rule to the row range and lock the column in the formula while leaving the row relative. For example, if column D is the test column and the selection begins on row 2, use a reference like =ISERROR($D2).

Will the highlight disappear after the error is corrected?
Normally, yes. When the cell no longer evaluates to an error, the ISERROR rule becomes false and its conditional formatting no longer applies. If a color remains, check for other rules or manually applied formatting.

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.