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

The rules that you set up for meeting these formatting conditions can vary widely. You can set up a rule whereby a particular type of formatting is applied when a cell in the range contains a certain text entry (such as Fixed or Variable). You set up a rule whereby a particular type of formatting is applied when a cell in the range is exactly a particular value or exceeds or falls below a particular value. So too, you can set up a rule whereby a par- ticular type of formatting is applied when the value is one of the top ten in the range, is below the average value in the range, or falls into the lower ten percent.
For example, to set up the rule that Excel formats any cell within a range with a light red fill color and dark red font color whenever it contains the word Fixed, you follow these steps:
1. Select the range of cells in the worksheet to which this conditional for- matting rule is to be applied.
2. Click the Conditional Formatting button on the Ribbon’s Home tab and then choose Highlight Cell Rules➪Text That Contains from the drop-down menu.
Excel opens the Text That Contains dialog box with a text box on the left where you enter or select in the worksheet the text that tells Excel when to apply the conditional formatting and a drop-down list box on the right where you select or define the conditional formatting the program is to apply.
3. Type Fixed in the Format Cells That Contain the Text box.
In this case, you don’t have to change the formatting in the drop-down
list box, as Light Red Fill with Dark Red Text is the default formatting.
4. Click OK to apply the conditional formatting rule to the selected cell range.
Say you wanted to apply three different types of conditional formatting to the cells in a single range of the worksheet: one type of formatting when- ever a cell in the range contains a target value, another when it exceeds this target value, and third when it falls below the target value.
Here are the steps for setting up the rules to apply a yellow fill with a dark yellow font to cells in a range when they contain 100,000, a green fill with dark green text when they’re greater than 100,000, and a light red fill with dark red text when they’re less than 100,000:
1. Select the range of cells in the worksheet to which the three condi- tional formatting rules are to be applied.
Start by defining the rule that applies yellow fill with dark yellow font to all values in the range that are equal to 100,000.
Conditional Formatting 177
Book II Chapter 2
Formatting Worksheets