How to expose expressions that return a defined error value in Microsoft Excel

Whether you’re starting on a new spreadsheet or adding something new in Microsoft Excel, errors happen. That’s why one of the first things I do is add conditional formatting to display errors in formulas. 

Then, when you’re ready to distribute the sheet, disable or delete it. What constitutes an error will be up to you of course, and for most of us, expressions that don’t evaluate because of missing or incorrect values or references are errors. In this article, I’ll show you a simple conditional formatting rule that displays all formulas that return a valid error message. 

SEE: 60 Excel tips every user should master (TechRepublic)

I’m using Microsoft 365 on a Windows 10 64-bit system, but you can use earlier versions. You can work with your own data or download the demonstration .xlsx file. The browser edition will display this conditional format and the green triangles, but it doesn’t support the Go To Special option.

About error messages in Excel

When working in Excel, you’ll probably want to expose expressions that return error values—built-in values that let you know that something’s not right. You’ve probably seen a few of those listed in Table A before. Knowing what these values mean offers a clue to troubleshooting; however, they are easy to miss! That’s why you might want to use conditional formatting that highlights these error values.

Table A

Adding the conditional formatting rule in Excel

Using a conditional format to highlight error values is easy; choosing the color might take you more time than implementing the rule! The sheet in Figure A has several expressions that return error values. In this simple sheet, they’re easy to find, but in a large busy sheet, you might miss them. As a result, any information you glean is at risk. We’ll add one simple conditional formatting rule that highlights them all—that’s right, it only takes one.

Figure A

Now, let’s apply the conditional format rule as follows:

1. Select the expression cells. In this case, that’s D3:D10.
2. On the Home tab, click Conditional Formatting in the Styles group and choose New Rule.
3. In the top pane, chose the Use a Formula to Determine… option.
4. In the lower pane, enter the following formula:
   =ISERROR($D3)
5. Click Format.
6. Click the Fill tab, choose a color (I choose yellow), and click OK. Figure B shows the ISERROR() function and the format
7. Click OK to return to the worksheet, shown in Figure C.

Figure B

Figure C

Any expression in the selected range (step 1) that returns an error values will be highlighted with the color you chose in step 6. At this point, you can decide whether to add error-handling to suppress the error value or to leave it as is while you continue to work. This format stays in place; every time you launch the sheet, the highlights will be there, until you disable the rule. This conditional format route is easy, but there are other ways.

About the green triangles in Excel

Did you notice the green triangles in the top-left corner of the error cells? This is a built-in feature that automatically highlights cells with an error value, among other things. When you select the cell, Excel displays a small icon to the left with an exclamation point. That icon is called a trace error button. Click it to display the list shown in Figure D. The result is a list of possible errors. As you can see, in cell Divide by Zero Error is the first option in the list.

Figure D

These triangles stay in place until you resolve the error or disable the feature. To do the latter, click the File tab and choose Options. In the resulting dialog, choose Formulas in the left pane. In the Error checking section, uncheck the Enable Background Error Checking option (Figure E), and click OK. So far, we’ve seen two permanent ways to expose value errors. What if you only need a quick spot-check?

Figure E

Shine a spotlight

Both the conditional format rule and the trace button stay in place until you do something about them. On occasion, you might want a quick spot-check, instead of something permanent. In this case, you can use the Go To Special feature as follows:

1. In the Editing group (on the Home tab), click Find & Select.
2. From the resulting list, choose Go To Special.
3. In the resulting dialog, click Formulas (Figure F).
4. From the sublist for Formulas, uncheck everything but Errors and click OK. Excel will apply a gray highlight to all cells with error values.

Figure F

This feature differs from the other two because the highlight disappears as soon as you do anything else. It provides a quick glimpse at error values, but it won’t persist while you work.

Stay tuned

I’ve shown you three ways to expose error values while you’re working, but you won’t want to distribute a sheet showing these errors. In a subsequent article, I’ll show you how to wrap formulas in error handling functions. 

Microsoft Weekly Newsletter

Be your company’s Microsoft insider by reading these Windows and Office tips, tricks, and cheat sheets.
Delivered Mondays and Wednesdays

Sign up today

Also see