Let’s say your spreadsheet formulas have errors that you anticipate and don’t need to correct, but you want to improve the display of your results. There are several ways to hide error values and error indicators in cells.
There are many reasons why formulas can return errors. For example, division by 0 is not allowed, and if you enter the formula =1/0, it returns #DIV/0. Error values include #DIV/0!, #N/A, #NAME?, #NULL!, #NUM!, #REF!, and #VALUE!.
There are many ways to hide such values. Here are some methods to do the same.
Option 1:
Format text in cells that contain errors so that they don’t show. Follow the seteps shown below.
1. Select the cells that contain the error value.
2. On the Format menu, click Conditional Formatting.
3. In the box on the left, click “Formula Is”.
4. In the box on the right, type =ISERROR(“reference”), where “reference” is a reference to the cell that contains the error value.
5. Click “Format”, and then click the “Font” tab.
6. Click “Format”.
7. In the “Color” box, select white.
8. Click ok.
Option 2 :
Display a dash, #N/A, or NA in place of an error value
To do this task, use the IF, ISERROR, and NA functions.
1. Select the cell that contain the error value.
2. Wrap the following formula around the formula in the cell, where “old_formula” is the formula that was previously in the cell.
=IF(ISERROR(“old_formula”),””,”old_formula”)
3. Enter.
This formula will check if the result of the formula is error, it will leave the cell blank. Else it will show the result.
Leave a Reply