To highlight errors in Excel, select the cells you want checked, then choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format. If the selected range starts at A2, enter =ISERROR(A2), choose a format, and confirm. Excel will apply that format to cells containing error values such as #VALUE! or #DIV/0!.
Set Up an Error-Highlighting Rule
The formula in the rule must refer to the first cell in the range you selected. For example, if your worksheet has formulas or imported values in A2:A100, select that range before creating the rule and use the formula shown above. Excel adjusts the relative reference for each cell: when it checks A3, it effectively evaluates whether A3 is an error, and so on.
- Select the cells to check. You can select one column, a block of cells, or a larger range.
- On the ribbon, choose Home > Conditional Formatting > New Rule.
- Choose Use a formula to determine which cells to format.
- Enter the formula using a reference to the top-left cell of the selected range.
- Select Format, choose options such as a fill color or bold font, and select OK.
- Confirm the rule in the New Formatting Rule dialog. If needed, review its range in Conditional Formatting Rules Manager.
Conditional formatting changes how a cell looks; it does not alter the underlying value or repair the formula that produced an error. This makes it useful for drawing attention to problems in a report without replacing or removing the evidence of the problem.
How ISERROR Works in Conditional Formatting
ISERROR is a logical function: it returns TRUE when its argument evaluates to an Excel error and FALSE otherwise. A conditional-formatting formula uses that result to decide whether Excel should apply the selected appearance. The formula does not need to return a color or text; it needs to return TRUE for the cells that should receive the format.
Excel error values include familiar results such as #DIV/0!, #N/A, #NAME?, #NUM!, #REF!, #VALUE!, and #NULL!. Newer Excel features can also produce errors such as #SPILL! or #CALC!. ISERROR is designed to detect error values generally, so it is usually the simplest choice when the goal is to highlight any error rather than a particular kind.
There is an important distinction between an actual Excel error and text that merely looks like one. A cell containing the literal text #N/A is text, not an error value, so ISERROR will return FALSE for it. By contrast, a formula such as a lookup that returns the actual #N/A error is detected. This difference matters when data has been copied from a website or imported from a text file.
A formula that returns an empty string, such as a formula designed to display nothing, is not an error either. It will not be highlighted by an ISERROR rule. That is usually desirable: blank-looking results and error results communicate different things.
Examples for Columns and Whole Rows
For a single column, select the range first and use its top-left cell in the rule. With error-checking cells in C5:C200, select C5:C200 and use =ISERROR(C5). The reference is relative, so Excel checks each cell in the selected range in turn. If the first selected cell were C1 instead, the formula should refer to C1.
You can also color an entire record when one particular column contains an error. Suppose a table occupies A2:F100 and column D contains the calculation to monitor. Select A2:F100, then create a formula rule using =ISERROR($D2). The dollar sign fixes the column at D, while the row number remains relative. Consequently, each row is formatted according to the error status of its own D cell.
To highlight a row when any of several monitored cells contains an error, use OR with an ISERROR check for each relevant column. For a selected range A2:F100 where columns B, D, and F are the cells to monitor, use =OR(ISERROR($B2),ISERROR($D2),ISERROR($F2)). This approach is explicit and easy to audit. It is often more useful than highlighting every cell in a row, because a reader can see the full record while the formatting signals where attention is needed.
For a range with many columns, consider applying a separate rule to the cells that can actually contain formulas or imported results rather than coloring every cell in the worksheet. Focused ranges make the rule easier to understand and reduce the chance that unrelated content is formatted.
Choose the Right Range and Format
The Applies to range determines which cells can receive the formatting. A correct formula paired with the wrong range can make a rule appear broken. For instance, a rule intended for rows 2 through 100 may start at row 1, shifting the formula’s row references. Check that the first cell in Applies to matches the relative reference used in the formula.
Use a visual treatment that makes errors noticeable without making a busy worksheet difficult to read. A pale red fill, a contrasting font color, or a simple bold treatment can work. The format is only a visual cue: it does not add a warning message, change a cell’s number format, or prevent someone from editing the cell.
If your data is an Excel Table, you can apply a conditional-formatting rule to the relevant table column or data range. When the table grows, Excel often extends formatting with the table, but check the rule’s Applies to range if new rows are not highlighted. Structured references can be useful in worksheet formulas, but a basic cell reference in the conditional-formatting formula is usually easier to troubleshoot.
Manage Rules and Avoid Common Problems
When multiple conditional-formatting rules apply to the same cells, rule order and settings can affect what you see. Open Home > Conditional Formatting > Manage Rules to inspect the rules, their order, and their Applies to ranges. In the dialog, use the Show formatting rules for control to display rules for the current selection or the worksheet. If another rule supplies a conflicting fill or font format, adjust the order or revise the formats.
The Stop If True setting can prevent lower-priority rules from being evaluated after a higher-priority rule is true. If an error rule seems not to apply, check whether an earlier rule stops evaluation. Also check whether the selected format is visible against the cell’s existing fill or font color; a rule can be working while its appearance is hard to notice.
- Nothing is highlighted: Check that the formula refers to the top-left cell of the selected range, and verify that the range includes the cells you expect.
- The wrong rows are colored: Check the row and column references. Use dollar signs only where a reference should stay fixed, such as $D2 for column D across multiple rows.
- Text that looks like an error is ignored: Confirm whether the cell contains an actual error value or text characters that resemble one.
- Some errors are missed: Check for rules that cover only part of the data, or cells whose formulas have been replaced with text or fixed values.
- Colors conflict: Review rule priority and Stop If True in Manage Rules, then simplify overlapping formats if needed.
Conditional formatting normally recalculates as worksheet values change. If a workbook’s calculation mode is set to Manual, however, formula results may not update until calculation is triggered. On Windows, press F9 to calculate open workbooks; on Mac, the corresponding shortcut is usually Fn+F9, depending on the keyboard settings. A stale formula result can make the displayed error status—and therefore the conditional format—seem out of date.
Conditional Formatting Versus IFERROR
Conditional formatting is best when you want to preserve an error value but make it visible. It helps during review, auditing, and troubleshooting because the original error remains available for inspection. It is a presentation and attention tool, not a correction.
IFERROR serves a different purpose: it replaces an error result with a value you specify. For a calculation in B2 that divides A2 by the value in C2, the formula =IFERROR(A2/C2,0) returns the division result when successful and 0 if the calculation returns an error. This can be appropriate when zero is a meaningful fallback, but it can also conceal a broken reference or an unexpected input. Decide whether the error should be fixed, exposed, or intentionally replaced before using IFERROR.
ISERR is another related function. It detects errors but, unlike ISERROR, does not treat #N/A as an error match. Use ISERR only when you specifically want to leave #N/A values unformatted while catching other errors. For most general error-highlighting tasks, ISERROR better matches the request.
There is no need to wrap an error-highlighting formula in IFERROR: ISERROR is already designed to test for errors. Also, conditional formatting cannot tell whether an error is harmless or serious. A #N/A result may be an expected signal that a lookup found no match, while #REF! may indicate a deleted reference. The right response depends on what each error means in the worksheet.
Excel for Mac and Excel for the Web
In Excel for Microsoft 365 on Windows, the ribbon path is Home > Conditional Formatting > New Rule. Excel for Mac also offers conditional-formatting rules and formula-based tests, although dialog layouts and some labels can differ by release. If the dialog presents rule categories rather than the Windows wording, choose the formula-based rule option and enter the same formula relative to the top-left cell of the range.
Excel for the web also supports conditional formatting, though the available controls and dialog presentation may differ from the desktop app. If you cannot find the formula-rule controls in the current web interface, open the workbook in the desktop application to create or edit the rule. Conditional formatting is not a macro, and setting up this rule does not require VBA.
Frequently Asked Questions
Can I highlight only #N/A errors?
Yes. With the first cell of the selected range in A2, use =ISNA(A2). ISNA is intended to test specifically for #N/A; use ISERROR instead if you want to catch all error types.
Can I highlight errors in an entire row?
Yes. Select the rows or table range to format, then make the formula check the relevant error cell with a fixed column and relative row. For example, if column D is the test column and the selected range begins on row 2, use =ISERROR($D2).
Does conditional formatting fix the error?
No. It changes the cell’s appearance only. Edit the formula, correct the input, restore a missing reference, or use an intentional error-handling formula if the error should be replaced.
Why is the text #VALUE! not being highlighted?
It may be text rather than an actual Excel error. ISERROR detects error values, not ordinary text that happens to contain the same characters. Check how the value was entered or imported before changing the rule.
Will the rule include new rows I add?
It may, especially when applied to an Excel Table, but do not assume the range expanded correctly. Open Manage Rules and verify the Applies to range after adding data. If necessary, edit the range so it includes the new rows.
Related
- Comparison Spreadsheet Template Excel: Setup and Examples
- Comparison Table Excel Template: Build, Format, and Use
- Comparison Excel Sheet Template: Build a Clear Side-by-Side
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Excel Cell Margins: Add Top and Bottom SpaceExcel does not have a separate top or bottom cell-margin setting. To create space inside a cell, adjust…
- Conditional Formatting Greater Than: Excel Rules ExplainedIn Excel for Microsoft 365 on Windows, use Home > Conditional Formatting > Highlight Cells Rules > Greater…
- Can You Goal Seek Multiple Cells at Once in Excel?Yes—but Excel’s built-in Goal Seek can change only one input cell to reach a target in one formula…
- Excel Goal Seek With Multiple Variables: When to Use SolverNo: Excel’s Goal Seek changes only one input cell to make a formula reach a target. To adjust…
- Excel 3-Color Scale: How It Works and When to Use ItIn Excel for Microsoft 365 on Windows, select the numeric cells, then choose Home > Conditional Formatting >…
- Employee Schedule Template for Google Sheets: SetupAn employee schedule template in Google Sheets is a reusable spreadsheet for assigning shifts, tracking hours, and sharing…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.