Copyright infringement

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

You can’t distribute an Excel sheet that shows ugly error values ​​when something goes wrong! Learn how to unravel these values ​​in action, so you can select the right fix.

Frustrated spreadsheet user

Photo: Pheelings Media, Getty Images/iStockPhoto

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

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

We see: 60 Excel Tips Every User Should Master (TechRepublic)

I’m using Microsoft 365 on Windows 10 64-bit, but you can use earlier versions. You can work with your own data or download the .xlsx demo file. This browser version will display conditional formatting and green triangles, but does not support the Go To Special option.

About error messages in Excel

When working in Excel, you may want to view expressions that return Error values—Built-in values ​​let you know that something is wrong. You may have seen quite a few of those listed in Table A Before. Knowing what these values ​​mean provides a guide to troubleshooting; However, it is easy to miss! That’s why you might want to use conditional formatting that highlights these error values.

Table A

Error

Explanation

# DIV / 0!

Displayed when the expression tries to divide a value by 0 or the cell is empty.

#Noun?

Displayed when the expression refers to a non-existent name range or incorrectly uses quotation marks in the text, which causes Excel to attempt to evaluate the text as a named range.

#void!

Displayed when multiple references are separated by a space instead of a math operator.

#NUM!

Displayed when a problem occurs with a number, such as the wrong data type.

#REF!

Displayed when an invalid cell reference occurs. This may happen if you delete a cell or paste cells on top of the originally referenced cells.

#the value!

Displayed when the wrong data type or operator is used, or if you try to evaluate a calculation on the text.

#N/A

Returned when the expression cannot be evaluated correctly; It’s kind of generic.

Add a conditional formatting rule in Excel

Using conditional formatting to mark error values ​​is easy; Choosing a color may take longer than implementing the rule! the paper in Figure A Many expressions that return error values. In this simple sheet, it is easy to find, but in a large, crowded sheet, you might miss it. As a result, any information you obtain is at risk. We’ll add a simple conditional formatting rule that highlights them all – that’s right, it only requires one.

Figure A

excelcferrorvalues-a.jpg

It can be difficult to find the error values.

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

  1. Select expression cells. In this case, it’s D3: D10.
  2. On the Home tab, click Conditional Formatting in the Styles group and choose New Rule.
  3. At the top, use a formula to select the… option.
  4. At the bottom, enter the following formula:
    = ISERROR ($D3)
  5. Click Format.
  6. Click the Fill tab, choose a color (I’m choosing yellow), then click OK. Figure B Displays the ISERROR() function and the formula
  7. Click OK to return to the worksheet shown in . format Figure C.

Figure B

excelcferrorvalues-b.jpg

See formula rule and formula.

Figure C

excelcferrorvalues-c.jpg

The error value cells have a yellow highlight.

Any expression in the specified range (Step 1) that displays error values ​​will be highlighted in the color you chose in Step 6. At this point, you can decide whether to add error handling to prevent the error value or leave it as it is while you continue to work. This shape stays in place; Each time you turn the sheet, the highlights will be there, until you disable the rule. This conditional formatting path is easy, but there are other ways.

About green triangles in Excel

Did you notice the green triangles in the upper left corner of the error cells? This is a built-in feature that automatically highlights cells with an error value, among other things. When the cell is selected, Excel displays a small icon to the left with an exclamation point. This code is called the tracing error button. Click it to display the menu displayed in figure d. The result is a list of potential errors. As you can see, in the cell Divide by Zero Error is the first option in the list.

figure d

excelcferrorvalues-d.jpg

Use the error trace button to learn more about the error.

These triangles remain in place until the bug is resolved or the feature is disabled. To do the latter, click on 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 just need a quick scan?

Figure E

excelcferrorvalues-e.jpg

Disable error tracing feature.

Highlight

The conditional formatting rule and the trace button remain in place until you do something about them. Sometimes, you may want to do a quick scan, rather than a permanent one. In this case, you can use the Go To Special as follows:

  1. In the Editing group (on the Home tab), click Find and Select.
  2. From the resulting menu, choose Go To Special.
  3. In the resulting dialog, click Formulas (shape and).
  4. From the Formulas submenu, uncheck everything except errors and click OK. Excel will apply a gray highlight to all cells that contain error values.

shape and

excelcferrorvalues-f.jpg

Temporarily highlight cells with error values.

This feature differs from the other two in that the distinction disappears as soon as you do anything else. It provides a quick overview of the error values, but it will not persist while you are working.

keep following

I’ve shown you three ways to expose error values ​​as you work, but you won’t want to hand out a sheet that shows these errors. In a later article, I’ll show you how to wrap formulas in error handling functions.

see also