To highlight cells that contain Excel errors, create a conditional-formatting rule using =ISERROR(A2). This example assumes the range starts at A2; select the range you want to format, then make A2 the formula’s relative reference. The rule highlights errors such as #N/A, #VALUE!, and #DIV/0! without changing the cell contents.
Set Up the Error-Highlighting Rule
In Excel for Microsoft 365 on Windows, select the cells to check and go to Home > Conditional Formatting > New Rule. Choose Use a formula to determine which cells to format, enter the formula, select Format to choose a fill, font, or border, and confirm the dialogs. The formula must return TRUE for a cell when you want the format applied.
For example, with possible errors in A2:A100, select A2:A100 before creating the rule. The formula =ISERROR(A2) checks each cell in that range. Because A2 is the first cell in the selected range, Excel adjusts the reference for each subsequent row: the rule checks A3, A4, and so on. Review the rule’s Applies to range in Home > Conditional Formatting > Manage Rules if you need to confirm or change where it applies.
On a Mac, use the Home > Conditional Formatting controls to create a new formula-based rule; the dialog’s appearance can differ from Windows, but the same formula logic applies. In Excel for the web, the available rule controls may vary from the desktop app. If a workbook needs detailed rule management or its menu is missing an option, open it in desktop Excel.
Which Excel Errors Does ISERROR Find?
ISERROR returns TRUE when its argument evaluates to an Excel error. It covers common errors such as #DIV/0!, #N/A, #NAME?, #NULL!, #NUM!, #REF!, and #VALUE!, as well as newer calculation errors such as #SPILL! and #CALC! where those errors are supported. It also detects an error stored in a cell even if the error came from a formula elsewhere.
The function does not treat ordinary text as an error. A cell containing the characters #N/A as text is not the same thing as the actual #N/A error value. Likewise, a genuinely empty cell or a formula that returns an empty text string is not an error, so the basic rule will not highlight either one. This distinction matters when data has been imported from another system and error-looking labels are simply text.
Conditional formatting only changes how a cell looks. It does not repair the formula, replace the error value, prevent the error from appearing in a formula bar, or change calculations that use that cell. Use it as a visual signal to investigate a problem, not as a substitute for fixing or handling the underlying calculation.
Choose the Right Cell Reference
The reference in a formula-based rule is interpreted relative to the top-left cell of the rule’s Applies to range. If the selected range begins at A2, =ISERROR(A2) checks each cell individually. If the selected range begins at C5, use C5 as the starting reference instead. A common cause of surprising results is entering a formula that points to a different starting cell than the range actually uses.
To highlight an entire row when one particular column contains an error, lock the column but leave the row relative. With a table whose records occupy rows 2 through 100 and whose status values are in column B, select the rows or cells to format, such as A2:F100. The formula is =ISERROR($B2). The dollar sign keeps the check in column B as Excel evaluates cells across a row, while the row number changes for each record.
For a row to be highlighted if any of three cells in columns B through D contains an error, use a formula that tests each of those cells. With records starting on row 2, and the rule applied to the rows beginning at A2, use =OR(ISERROR($B2),ISERROR($C2),ISERROR($D2)). The row references move as the rule evaluates each record, while the column references remain fixed. For larger sets of columns, consider applying separate rules or carefully testing a formula before extending it across a large range.
Dollar signs control which parts of a reference move: A2 is fully relative, $A$2 is fully fixed, and $A2 fixes only the column. For a single-cell check, absolute references can be appropriate, but using =$A$2 in a rule applied to many cells makes every cell check A2. That is usually not the intended behavior for highlighting individual errors.
Target One Particular Error Type
If the rule should highlight only missing-lookup errors, use ISNA rather than ISERROR. With A2:A100 as the range and A2 as its top-left cell, the formula is =ISNA(A2). It returns TRUE for #N/A and FALSE for other error types. This is useful when a missing match is expected during data entry but other errors should remain visually distinct.
Use ISERROR when every error deserves attention, and use an error-specific test when the workbook has different meanings for different errors. For instance, a lookup result of #N/A may indicate an unmatched item, while #REF! can point to a broken reference. Separate rules can assign different colors to those conditions. Avoid making a rule more elaborate than needed: a simple, targeted test is easier for another workbook user to understand and maintain.
Common Problems and How to Fix Them
- The wrong cells are highlighted: Check the top-left cell of the Applies to range and make sure the formula begins with a reference to that cell. Then inspect whether the row or column should be locked with dollar signs.
- No errors are highlighted: Confirm the cells contain actual Excel error values, not text that looks like an error. Also check that the rule is enabled, its applied range includes the cells, and another rule is not overriding the visible format.
- Every cell gets the same result: A fully absolute reference, such as
$A$2, makes every evaluated cell inspect A2. Use a relative reference for cell-by-cell checks or a partially fixed reference for row-based checks. - The rule works in one row but not another: Look for an incorrect starting row in the formula, an interrupted applied range, or a different formula pattern in the affected cells. Conditional formatting tests the cell values; it does not infer which cells belong to the same logical record.
- The format does not appear to change: Open Manage Rules and review the rule order, the Stop If True setting, and the format assigned to the rule. A later rule or another applied format can make the expected color hard to see.
When debugging, test the idea in a spare worksheet cell first. With the value to inspect in A2, enter =ISERROR(A2) in another cell. TRUE confirms that Excel recognizes an error there; FALSE means the conditional-formatting formula will not match that cell. Delete the test formula when you are finished if it is not part of the worksheet design.
Conditional Formatting Compared with IFERROR
ISERROR in conditional formatting identifies an error for display while leaving the original value intact. IFERROR, by contrast, is used inside a worksheet formula to return an alternative result when its first argument produces an error. These functions address related but different needs: one flags a cell visually, while the other changes what a formula returns.
For example, with a calculation in A2 that you want to display as blank when it errors, wrap that calculation in IFERROR and choose the appropriate fallback value. A blank-looking result can make a report cleaner, but it can also hide the fact that something went wrong. If you need users to investigate failures, highlighting the error or showing a clear message may be more informative than suppressing it. A workbook can also use error handling in a calculation and conditional formatting for other cells that still need review.
Do not use an error-handling formula as though it were the same as an error-detection rule. In particular, conditional formatting does not require you to rewrite every worksheet formula. Add a formula-based formatting rule when the goal is a visual alert; revise the worksheet formula when the result itself needs to change.
Keep Rules Clear and Manageable
A single rule applied to a modest range is usually straightforward. In a large workbook, many overlapping rules can make formatting harder to troubleshoot and may add calculation work, especially when rules use complex formulas over large ranges. Keep the applied range focused on the cells that need checking, avoid duplicating identical rules, and use clear colors that remain legible with the worksheet’s existing formatting.
After inserting rows, copying cells, or editing a range, revisit Conditional Formatting > Manage Rules. Check whether the applied range still covers the intended data and whether the formula’s references remain aligned with its top-left cell. If you use an Excel table that grows as rows are added, verify that the rule extends to new rows as expected; behavior can depend on how the rule was originally created and how the table is edited.
Frequently Asked Questions
What formula highlights errors in Excel conditional formatting?
Use =ISERROR(A2) when A2 is the first cell in the range being checked. Select the range before creating the rule, and change A2 to match its top-left cell.
Can conditional formatting highlight only #N/A errors?
Yes. Use =ISNA(A2) when A2 is the top-left cell of the applied range. It targets #N/A rather than all error types.
Does ISERROR highlight blank cells?
No. A blank cell and a formula returning an empty string are not error values, so ISERROR returns FALSE for them. It highlights cells only when the value is an actual Excel error.
Can I highlight the whole row if one cell has an error?
Yes. Apply the rule to the rows you want formatted and use a reference that locks the error-check column while allowing the row to change. For example, if column B holds the values and the applied range starts on row 2, use =ISERROR($B2).
Does this rule fix or hide the error?
No. It changes formatting only. To change a formula’s displayed result when an error occurs, use an error-handling approach such as IFERROR in the worksheet formula; decide carefully whether hiding the error is appropriate for the workbook.
Related
- Excel Conditional Formatting for Upcoming Dates
- Conditional Formatting Excel Dates Before a Date Rule
- Excel 3-Color Scale: How It Works and When to Use It
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Errors & fixes
- Excel Sort Dates Wrong? Fix the Order and Find the CauseWhen Excel sorts dates into the wrong order, the usual cause is that some entries are stored as…
- Excel Cannot Save or Export XML Data? Fix the ErrorIf Excel says it cannot save or export XML data, first check whether you are saving the workbook…
- Excel UNIQUE Formula Not Working? Fix Common IssuesIf Excel’s UNIQUE formula is not working, first check that you have Microsoft 365 or Excel 2021 or…
- Excel Copy Formula Drag Down Not Working? Try These FixesIf dragging a formula down in Excel does nothing, first check that the fill handle is enabled: go…
- Excel Too Many Arguments Error: Causes, Fixes, and ExamplesExcel’s “You’ve entered too many arguments for this function” message means a formula supplies more inputs than that…
- Can’t Hyperlink in Excel? Create and Fix LinksYes, you can add clickable hyperlinks in Excel to websites, files, email addresses, and places in a workbook.…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.