Formats to Suit Every Condition
Conditional formatting enables you to set up two types of criteria that determine when the program applies the conditional formatting you designate:
- Cell Value Is: Compares the value (constant) you specify in the Conditional Formatting dialog box against the value entered in the cell. When Excel compares this constant to the cell value and the criteria you specify for them is met (is between, equal to, greater than, less than, greater than or equal to, or less than or equal to), Excel applies the conditional formatting to the cell. When the condition is not met, Excel uses the regular formatting applied to the cell.
- Formula Is: Evaluates the logical formula you enter in the Conditional Formatting dialog box. When this formula evaluates to TRUE, Excel applies the conditional formatting you define to the cell. When the formula evaluates to FALSE, Excel applies the regular formatting to the cell.
When you want to be warned when a cell contains a particular value or exceeds or falls below a certain number, the Cell Value Is type of conditional formatting is the way to go. To get an idea of how you would use this type of conditional formatting, follow along with the steps for displaying the entry in a cell in red with bold and strikethrough whenever it contains a negative value:
- Position the cell pointer in the cell where you want to apply conditional formatting.
- Choose Format → Conditional Formatting to open the Conditional Formatting dialog box.
When Excel opens this dialog box, the Cell Value Is option is automatically selected along with Between as the comparison operator. - Select Less Than in the second drop-down list box (the one that now contains Between).
- Press Tab to select the last text box and then type 0.
After setting up the condition for the special formatting, you must specify what formatting attributes to use. The Font attributes that you can set for conditional formatting are limited to font style, underlining, strikethrough, and color. For this example, I turn on strikethrough and bold and select red as the font color. - Click the Format button in the Conditional Formatting dialog box to open the Format Cells dialog box.
- On the Font, Border, and Patterns tabs, select the attributes to be used when the condition is true. When you're finished selecting your
attributes, click OK.
When you close the Format Cells dialog box, the Conditional Formatting dialog box shows your first condition along with a preview of the formatting that Excel will apply when the condition is true. At this point, you can either add another condition (see the next section, "When two conditions are better than one"), or if you need only the one condition - as is the case here - you can close the dialog box. - Click OK to close the Conditional Formatting dialog box and put the conditional formatting into effect in the current cell.
