You can't distribute an Excel sheet that displays ugly error values when something goes wrong! Learn how to expose those values while you work, so you can determine the right fix.
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.
Returned when the expression tries to divide a value by 0 or the cell is empty.
Returned when the expression refers to a name range that doesn't exist or uses quotation marks in text incorrectly, resulting in Excel attempting to evaluate the text as a named range.
Returned when multiple references are separated by a space instead of a mathematical operator.
Returned when a problem with a number occurs, such as the wrong data type.
Returned when an invalid cell reference occurs. This might happen if you delete a cell or paste cells over the originally referenced cells.
Returned when the wrong data type or operator is used or if you try to evaluate a mathematical operation on text.
Returned when an expression can't evaluate correctly; it's rather generic.
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.
Now, let's apply the conditional format rule as follows:
- Select the expression cells. In this case, that's D3:D10.
- On the Home tab, click Conditional Formatting in the Styles group and choose New Rule.
- In the top pane, chose the Use a Formula to Determine… option.
- In the lower pane, enter the following formula:
- Click Format.
- Click the Fill tab, choose a color (I choose yellow), and click OK. Figure B shows the ISERROR() function and the format
- Click OK to return to the worksheet, shown in 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.
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?
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:
- In the Editing group (on the Home tab), click Find & Select.
- From the resulting list, choose Go To Special.
- In the resulting dialog, click Formulas (Figure F).
- From the sublist for Formulas, uncheck everything but Errors and click OK. Excel will apply a gray highlight to all cells with error values.
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.
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.
- How to become a software engineer: A cheat sheet (TechRepublic)
- Zoom vs. Microsoft Teams, Google Meet, Cisco WebEx and Skype: Choosing the right video-conferencing apps for you (free PDF) (TechRepublic)
- Hiring Kit: Application engineer (TechRepublic Premium)
- Microsoft 365 (formerly Office 365) for business: Everything you need to know (ZDNet)
- The 10 most important iPhone apps of all time (Download.com)
- It takes work to keep your data private online. These apps can help (CNET)
- Must-read coverage: Programming languages and developer career resources (TechRepublic on Flipboard)