White-Out Errors with Conditional Formatting in Excel 2013

Instead of creating logical formulas in Excel 2013 to suppress the display of potential error values, you can use Conditional Formatting to deal with them. All you have to do is create a new conditional formatting rule that displays all potential error values in a white font (essentially, rendering them invisible in the cells of your worksheet).

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.

After applying the new conditional formatting rule to a cell range, you can test it out by deliberately entering an error value into one of the cells referenced in one of the formulas in that range now covered by the “white-out” conditional formatting. Entering the #NA error value in one of these cells with the =NA() function is perhaps the easiest to do.

Instead of seeing #NA values spread throughout the cell range, the cells should now appear empty because of the white font applied to all the #N/As, rendering them, for all intents and purposes, invisible.

blog comments powered by Disqus
Advertisement

Inside Dummies.com

Dummies.com Sweepstakes

Win $500. Easy.