Let Excel automatically hide display information for error results

Friends in Excel to count students ' scores, enter the formula in cell E63: =average (e3:e62), ready to calculate the average score, because the above cell has not entered the score data, so the formula returned an error result (see Figure 1).

Friends ask me, how to let these error results do not show or print out? I introduced him to the following three methods.

Figure 1

Method of function processing

As long as we use the IF and ISERROR functions to deal with the formula slightly, this error value is no longer displayed.

Modify the formula in cell E63 to: =if (ISERROR (AVERAGE (e3:e62)), "", AVERAGE (E3:E62)), and make sure it's OK.

Tip: The formula above means that if the (if) formula "=average (E3:E62)" Returns an error value (as judged by the ISERROR function), it is displayed as null (""), instead of the return result of the formula.

Second, conditional Format method

Set conditional formatting, when the formula returns an error value, the character color is set to white, and the error value is not displayed.

1, select E63 Cell, perform the format → conditional format command, print the Conditional Formatting dialog box (Figure 2), press the Drop-down button next to the leftmost box, select the Formula option in the Drop-down list that appears, and then enter the formula in the following box: =iserror (E63).

Figure 2

2, click the Format button, open the Cell Format dialog box (see Figure 3), under the Font tab, click the Drop-down button to the right of color, and then in the palette that pops up, select White to make sure it returns.

Figure 3

3, finally click the "OK" button in the Conditional Formatting dialog box, the error value is immediately hidden (white).

Third, the page Setup method

This is the easiest way to do this if you just want the error value not to be printed when you print it.

To perform the file → page Setup command, open the Page Setup dialog box (Figure 4), switch to the Sheet tab, press the Drop-down button on the right side of the error cell, and in the Drop-down list that appears, select the Blank option, and then make sure to return and print again.

Figure 4

Tip: Obviously, the above three methods can not only be applied to the average formula of this example.