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:
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.
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”.