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.

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,"")

Solution 2 / Conditional Formatting

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

Go to the New Rule… in Conditional Formatting menu.

Choose “Use a formula to determine which cells to format” and write this function:
=ISERROR(B9)

Now, click on the Format… button and change font color to white.

This is the result.

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.