Excel Conditional Formatting for Errors: Fix & Hide

Errors & fixes · Updated November 22, 2026

To highlight or hide errors in Excel, use Conditional Formatting with the ISERROR function (or ISNA for #N/A only) in a formula-based rule. For example, select your data range, go to Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format, enter =ISERROR(A1) (adjusting A1 to the top-left cell of your selection), set a format like a red fill, and click OK. This instantly marks every cell containing any error value (#DIV/0!, #N/A, #VALUE!, #REF!, #NAME?, #NUM!, or #NULL!).

Why Errors Appear and Why You Should Care

Excel shows an error value when a formula can’t compute a result. Common causes include dividing by zero (=B2/C2 with C2 empty or 0), referencing a deleted cell (#REF!), mistyping a function name (#NAME?), or using text where a number is expected (#VALUE!). Errors are useful signals during development, but they look unprofessional in reports and can confuse viewers. Conditional formatting lets you either draw attention to them (so you can fix them) or hide them (for a clean presentation).

How the ISERROR Formula Rule Works

Conditional formatting evaluates a formula for each cell in the selected range, starting from the active cell (the first cell you selected). The formula must be written relative to that top-left cell. If the formula returns TRUE, the format is applied. ISERROR returns TRUE for any error type; ISNA returns TRUE only for #N/A. For example, to highlight #N/A only, use =ISNA(A1). To highlight specific errors like #DIV/0! only, you can use =ISERROR(A1) combined with a nested check, but simpler is to use the built-in “Format only cells that contain” rule: choose “Errors” from the dropdown. That built-in rule is easier for most users and requires no formula.

Step-by-Step: Highlight All Errors

  1. Select the range you want to check (e.g., A2:F100).
  2. Go to Home > Conditional Formatting > New Rule.
  3. Choose “Format only cells that contain”.
  4. Under “Format only cells with”, set the first dropdown to “Errors”.
  5. Click Format…, choose a Fill color (e.g., light red) and/or Font color, then OK.
  6. Click OK again to apply the rule.

This rule highlights any cell in the range that currently contains an error. It updates automatically as you edit formulas. On Excel for Mac, the steps are identical; on Excel for the web, you can also use the same menu (Home > Conditional Formatting > New Rule).

Hide Errors with White Text or a Custom Format

If you want to make errors invisible (but keep the underlying formula), apply a format that blends with the cell background. For white cells, set the font color to white. But this also hides the error from screen readers and can confuse if the cell is later filled with a color. A better approach is to use the IFERROR function in the formula itself, which replaces errors with a custom message like =IFERROR(B2/C2, "") or =IFERROR(B2/C2, "N/A"). This is the recommended method for production reports because it prevents errors from ever displaying. However, conditional formatting with white text is a quick visual fix that doesn’t change the formula.

Using Conditional Formatting to Find Errors in a Large Dataset

When you have thousands of rows, manually scanning for errors is impractical. Create a rule that highlights errors in a bright color, then use the “Filter by Color” option (Data > Filter, then the filter arrow) to isolate those cells. Alternatively, use the “Go To Special” dialog (Ctrl+G, then Special > Errors) to select all error cells at once—this works without conditional formatting. Combining both: apply the formatting, then use Go To Special to quickly jump through each error cell with the Tab key.

Common Problems and Solutions

  • Rule doesn’t highlight anything: Make sure the range you selected is correct and that the active cell is the top-left of that range. If you used a formula like =ISERROR(A1) but selected a range starting at B2, adjust to =ISERROR(B2).
  • Errors are not updating: Conditional formatting recalculates when the worksheet recalculates. If you have manual calculation mode (Formulas > Calculation Options > Manual), press F9 to recalc.
  • Rule applies to blank cells: ISERROR returns FALSE for blanks, so that’s not an issue. But if you use a formula like =A1="" to hide blanks, it won’t catch errors.
  • Too many rules slow down the sheet: Each conditional formatting rule adds overhead. Use one rule per range, not one per cell. For huge datasets, consider using a helper column with ISERROR and then filter.

Comparison: Conditional Formatting vs. IFERROR vs. Go To Special

Conditional formatting is visual and non-destructive—it doesn’t change your data or formulas. IFERROR changes the output to a value you choose, which is permanent and affects other formulas that reference the cell. Go To Special is a one-time selection tool, not a persistent format. For ongoing error monitoring, conditional formatting is best. For final reports where you want no error text at all, wrap formulas with IFERROR. For a quick audit, use Go To Special.

Frequently Asked Questions

Can I highlight errors in only one column?

Yes, select just that column (e.g., C2:C100) and apply the same rule. The formula-based rule will work relative to the active cell in that selection.

How do I highlight errors that are not #N/A?

Use a formula rule with =AND(ISERROR(A1), NOT(ISNA(A1))). This returns TRUE for any error that isn’t #N/A. Or use the built-in “Errors” rule and then manually format #N/A differently with a second rule using =ISNA(A1).

Will conditional formatting slow down my workbook?

It can, especially if you have many rules or apply them to entire columns (e.g., A:A). Limit the range to actual data and avoid using volatile functions like OFFSET or INDIRECT in your rules.

Can I use conditional formatting to replace errors with a message?

No, conditional formatting only changes the appearance, not the cell value. To replace the error text, use IFERROR in the formula. For example, =IFERROR(B2/C2, "Check input").

Does this work in Google Sheets?

Yes, Google Sheets has a similar feature under Format > Conditional formatting. Use the “Custom formula is” option with =ISERROR(A1). The syntax is identical, but the menu path differs.

Why does my rule highlight blank cells as errors?

Blank cells are not errors, so ISERROR returns FALSE. If you see blanks highlighted, your formula might be referencing a cell that returns an error, or you accidentally applied the rule to a range that includes cells with formulas that return errors. Check the formula in the cell itself.

Safety and Best Practices

Before applying conditional formatting, always save a copy of your workbook—especially if you plan to use VBA to automate formatting. Conditional formatting itself is safe and can be undone with Ctrl+Z, but complex rules can be hard to debug. Also, remember that conditional formatting does not protect against errors; it only makes them visible. Always verify your formulas with sample data. If you share the file, note that conditional formatting rules are preserved in .xlsx files, but some third-party viewers may not display them.

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.