Page 364 - Excel 2013 All-in-One For Dummies

346 Whiting-Out Errors with Conditional Formatting
of the Excel Options dialog box (File➪Options or Alt+FT). By this action, the cells with error-trapping formulas remain blank until you give them the data that they need to return the correct answers!
Whiting-Out Errors with Conditional Formatting
Instead of creating logical formulas to suppress the display of potential error values, you can use Conditional Formatting (see Book II, Chapter 2
for details) to deal with them. All you have to do is create a new condi- tional formatting rule that displays all potential error values in a white font (essentially, rendering them invisible in the cells of your worksheet). I think of using conditional formatting in this manner to deal with possible error values in a worksheet as applying a kind of electronic white-out that masks rather than suppresses formula errors.
To create this conditional formatting white-out for cells containing formulas that could easily be populated with error values, you follow these steps:
1. Select the ranges of cells with the formulas to which you want the new conditional formatting rule applied.
2. Select the Conditional Formatting button on the Home tab and then choose New Rule from its drop-down menu (Alt+HLN).
Excel displays the New Formatting Rule dialog box.
3. Select the Format Only Cells That Contain option in the Select a Rule Type section at the top of the New Formatting Rule dialog box.
4. Choose the Errors item from the Cell Value drop-down menu under Format Only Cells With section of the New Formatting Rule dialog box.
The New Formatting Rule dialog box now contains an Edit the Rule Description section at the bottom of the dialog box with Errors displayed under the Format Only Cells With heading.
5. Click the Format button to the immediate right of the Preview text box that now contains No Format Set.
Excel opens the Format Cells dialog box with the Font tab selected.
6. Click the Color drop-down menu button and then click the white swatch, the very first one on the color palette displayed under Theme Colors and then click OK.
Excel closes the Format Cells dialog box and the Preview text box in the New Formatting Rule dialog box now appears empty (as the No Format Set text is now displayed in a white font).
7. Select OK in the New Formatting Rule dialog box to close it and apply the new conditional formatting rule to your current cell selection.