How to hide error values

In Excel table may occur some error values like #DIV/0, #N/A … But it does not necessarily mean an error in a formula. It could mean that there is some unfilled value or some value is not in table and so on. In this cases is better display nothing or some specific word or number instead of error value.  I have prepared table containing #DIV/0 values and two ways how to solve it. The first is using formula and the second is made by conditional formatting.

Table with error values

The bottom table have simple formula for calculating percentage of items revenue. For example in B9 is formula =B2/B$6. So, when cells in the top table is empty, result of formula is #DIV/0.

Solution 1 / Formula

We use IFERROR() function to find if the result is error value. So, change formula in every cell like this:

=IFERROR(B2/B$6,"")

Table without error values (solved by IFERROR)

Solution 2 / Conditional Formatting

Error value is hidden by changing the font color. First select the cells that might contain an error value.

Selection of possible error values

Go to the New Rule… in Conditional Formatting menu.

New rule in conditional formatting

Choose “Use a formula to determine which cells to format” and write this function:
=ISERROR(B9)
New rule
Now, click on the Format… button and change font color to white.

Font format

This is the result.

Table without error values (solved by Conditional Formatting)

If you want to hide a little green flag in cells, you can do it in Excel Options. Click Office Button on the top left and choose Excel Options. Then select Formulas and uncheck “Enable background error checking”.

Important: This setting applies to your Excel, so it will hide every green flag in each file you open. And on the other side when someone open this file in his Excel, green flags will be visible.
Error Checking Options

Add a comment »3 comments to this article

  1. I’ m looking for the general solution how to avoid errors in my Excel files.

    Reply

  2. Hi,

    I have to handle error in shared excel file. I cannot use Conditional Formatting or IFERROR function. Is there a different way how to catch error value in my shared spreadsheet? Thanks

    Reply

  3. I like the one using conditional formatting 🙂

    Reply



Copyright © All Rights Reserved · Green Hope Theme by Sivan & schiy · Proudly powered by WordPress