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!.
Format text in cells that contain errors so that they don't show:
- Select the range of cells that contain the error value.
- On the Home tab, in the Styles group, click the arrow next to
Conditional Formatting, and then click MANAGER RULES.
The Conditional Formatting Rules Manager dialog box is displayed.
- Click NEW RULE.
The New Formatting Rule dialog box is displayed.
- Under Select A Rule Type, click USE A FORMULA TO DETERMINE WHICH
CELLS TO FORMAT.
- Under Edit the Rule Description, in the Format values where this
formula is true list box, enter the following formula:
** Where reference is a relative reference to the cell that contains the
- Click FORMAT, and then click the FONT tab.
- In the Color box, select WHITE, click on
- Click on the Apply button on the Conditional Formatting Rules Manager