Conditional formats respond to the contents of cells. They are almost always applied to groups of cells—often rows or columns of totals—if not entire tables. Click Home, Conditional Formatting to display the menu shown in Figure 9-20.
Excel 2010 offers five flavors of formatting features you can use for your conditional creations:
Highlight Cells Rules Formatting you apply to cells that stays “asleep” until the values (numeric or text) the cells contain achieve the specified state. Click Greater Than, Less Than, Between, Equal To, Text That Contains, A Date Occurring, or Duplicate Values to display a dialog box where you can specify the appropriate criteria.
Top/Bottom Rules Selected formatting applied to all cells in a range that are greater than or less than a given threshold. Click Top N Items, Top N %, Bottom N Items, Bottom N %, Above Average, or Below Average to display a dialog box where you can specify the appropriate criteria.
Data Bars Gradient fills of color within cells. The bars’ lengths indicate the values in the cells relative to all other adjacent cells formatted using the same conditions. Choose from a number of different colors, based on the current theme.
Color Scales Two-color or three-color formats whose color indicates the values in the cells relative to all other adjacent cells formatted using the same conditions. Choose from a number of different color combinations, based on the current theme.
Icon Sets Sets of three, four, or five tiny graphic images placed inside cells. The icons’ shape or color indicates the values in the cells relative to all other adjacent cells formatted using the same conditions. Choose from a number of different types of icons.
For example, you could apply conditional formatting to a range of cells that contain sales totals, specifying that if any of the totals drops to less than $1,000, the format of the cell changes to stand out from the other cells. To do so, follow these steps:
Select the cells you want to format.
Click Conditional Formatting, Highlight Cells Rules, Less Than to display the dialog box shown in Figure 9-21.
Type the number you want to use as the threshold for this condition; in this case, 1000.
Select one of the options from the drop-down list of available formats.
Notice that when you select a format option from the Highlight Cells Rules menu, Excel previews it for you on the worksheet. Type a number in the Format Cells That Are Less Than box, select a format option from the drop-down list, and the preview adjusts accordingly. Clicking the Custom Format option at the bottom of the list displays a version of the Format Cells dialog box with the Number, Font, Border, and Fill tabs available.
Click OK.
Figure 9-21. Select the Less Than rule on the Highlight Cells Rules menu to create a stoplight chart using conditional formatting.
Figure 9-22 shows a table after applying conditional formatting. This example was formatted using two highlight cells conditions: one format for numbers greater than 9,000 and a different format for numbers less than 1,000.
Figure 9-22. We created two conditions—one to flag high values and one to flag low values. These guys had a rough January.
This procedure is essentially the same for all the highlight cells and top/bottom rules, but several of these rules deserve additional comment:
Between This is obvious perhaps, but although the Greater Than, Less Than, and Equal To rules require you to type a single number criterion, the Between rule requires two criteria.
Text That Contains When you choose this rule, cells containing any form of the text string you type as a criterion are highlighted (entering “and” highlights cells containing sand, Andrew, and so on).
A Date Occurring This rule always uses the current date as the point of reference. The “occurring” options are all relative to this: Yesterday, Last Week, Next Month, and so on.
Duplicate Values This rule actually has two options, highlighting either duplicate or unique values.
The highlight cells rules are the only ones that operate independently of other cells. That is, each cell is evaluated against criteria individually and formatted accordingly. All other conditional formats depend entirely on the rest of the cell values formatted using the same condition. For example, Figure 9-23 shows the same top/bottom rule applied to two different selected regions (in this case, we specified the top five).
As you can see in Figure 9-23, cell F10 drops out of the top five, and cell C14 is added to the top five when we select a different range of cells. Excel uses all the values in the selected cell range to determine which cells to format. For data bars, color scales, and icon sets, Excel actually applies formatting to every cell in the selected range but adjusts the color, size, or icon based on each cell’s value relative to the whole.
Data bars are a unique type of conditional format because each cell actually contains the same color (actually, a gradation of color), but the size of the colored area varies in each cell to reflect the cell’s value relative to the other selected cells. Figure 9-24 shows a live preview of the orange data bar.
All these conditional formats are pretty flashy, and they definitely help identify relative values in a range, but you can begin to see that too much conditional formatting can become counterproductive. As with any flashy feature, it’s easy to love it a little too much, so make sure you’re serving the purpose of your worksheet. Figure 9-25 shows what might be considered a more judicious application of conditional formatting using highlight cells and data bars.
Figure 9-24. You can rest the pointer on items on the Data Bars menu to see a live preview on your worksheet.
Excel provides quite a nice variety of conditional formatting options, but you can always create your own as well. You may have noticed the New Rule command at the bottom of the Conditional Formatting menu and the ubiquitous More Rules command on each submenu. These all do essentially the same task—display the New Formatting Rule dialog box shown in Figure 9-26.
All these commands open the same dialog box, but based on the menu or submenu where you clicked the command, a different rule type is selected when it opens. Each rule type displays a different set of rule-description criteria below it. To display the dialog box in Figure 9-26, we clicked Conditional Formatting on the Home tab, then Icon Sets, and then the New Rule command. Each format style has a different set of controls for creating conditional formatting rules.
The first rule type—Format All Cells Based On Their Values—contains all the controls for creating data bars, color scales, and icon sets. The rule description controls for the second rule type—Format Only Cells That Contain, shown in Figure 9-27—is what you use to create highlight cells rules. The controls for the remaining rule types are similar to this one.
Note
We discuss the last rule type, Use A Formula To Determine Which Cells To Format, in Creating Conditional Formatting Formulas on page 319.
Figure 9-27. Use the second rule type in the New Formatting Rule dialog box to create your own highlight cells rules.
Use the Edit The Rule Description area to define your formats. This area changes depending on the rule selected. With Format Only Cells That Contain selected, the first drop-down list in this area (shown in Figure 9-27) allows you to define rules for highlighting Specific Text, Dates Occurring (relative to now), Blanks, No Blanks, Errors, or No Errors. Then, select an operator (Between, Not Between, Equal To, Not Equal To, Greater Than, Less Than, Greater Than Or Equal To, Less Than Or Equal To) and type comparison values in the remaining text boxes.
After you establish the rule description criteria, click the Format button. An abbreviated version of the Format Cells dialog box appears, containing only Number, Font, Border, and Fill tabs. Specify any combination of formats you want to apply when your rule is triggered. When you finish, click OK to return to the New Formatting Rule dialog box, and click OK again to save your new rule. You can create as many rules as you want; next, we’ll discuss how to work with them.
You can apply as many conditional formats as you think are necessary—using three or more per table is not uncommon. But it is also not uncommon for you to tweak some of the numbers or adjust some of the formatting. To do so, click Home, Conditional Formatting, Manage Rules to display a Conditional Formatting Rules Manager dialog box similar to the one shown in Figure 9-28.
Figure 9-28. Use the Conditional Formatting Rules Manager dialog box to tweak any rules that have been applied in a workbook.
You can use the Show Formatting Rules For drop-down list at the top of the dialog box to choose where to look in the current workbook for rules: Each worksheet in the current workbook is listed here, or you can choose This Worksheet or Current Selection (the default). As you can see in Figure 9-28, you can create, edit, and delete rules by using corresponding buttons. When you click New Rule, the now-familiar New Formatting Rule dialog box appears. When you click Edit Rule, a similar dialog box appears (Edit Formatting Rule) with the criteria for the selected rule displayed.
Excel applies the rules listed in the Conditional Formatting Rules Manager dialog box in the order in which they appear in the Rule list—new rules are added to the top of the list and are processed first. Use the two arrow buttons next to Delete Rule to move a selected rule up or down in the precedence list. The Applies To box contains the address of the cell range to which the rule is applied. If you want to change the cell range, click the Collapse button at the right end of the Applies To text box to collapse the dialog box, letting you see the worksheet, as shown in Figure 9-29. When you do so, you can drag to select the cell range you want and insert the range address in the text box. To restore the dialog box to its original size, click the Collapse (now Expand) button again.
The Stop If True check box is present in this dialog box only for backward compatibility. Previous versions of Excel cannot recognize multiple conditional formatting rules, and instead they apply the rule that occurs last in precedence. If you need to share files with older versions, you need to choose which conditional formatting rule you prefer. Select the Stop If True check box for the last rule in the list if you want Excel to use the previous rule; select the Stop If True check box for the last two rules to use the third-to-last rule, and so on.
Figure 9-29. Click the Collapse button in the Applies To text box to minimize the dialog box and allow direct selection of the cell range you want.
Note
When two (or more) conditional rules are true for a particular cell, but they are both set to apply a similar format, such as font color, the rule that is higher in the Conditional Formatting Rules Manager dialog box’s list of precedence wins. Try to make multiple conditions compatible by having each rule apply a different type of format, such as the first rule applying cell color, the second applying font color, and the third applying bold formatting. In addition, conditional formats override manual formats when the condition in the cell is true.
You can copy and paste conditionally formatted cells and use the Fill features or the Format Painter button to copy cells that you have conditionally formatted. When you do, the conditional rules travel with the copied cells, and a new rule is created that references the new location in the workbook.
To remove conditional formatting rules, click Home, Conditional Formatting, Clear Rules, and then click Clear Rules From Selected Cells or Clear Rules From Entire Sheet to clear all the corresponding rules. If your conditions have been applied to a table or a PivotTable, additional corresponding commands are available.
You can use two commands on the Find & Select menu on the Home tab to locate cells on the current worksheet that conditional formats are applied to. The Conditional Formatting command locates and selects all the cells on the current worksheet to which conditional formats are applied. If conditional formatting exists in more than one cell region on the worksheet, using this command selects all the regions. This makes it easy to edit all the rules using the Conditional Formatting Rules Manager dialog box (refer to Figure 9-28). You can also use the Go To Special command on the Find & Select menu to get a little more specific. Clicking this command displays the Go To Special dialog box, shown in Figure 9-30.
Figure 9-30. Use the Go To Special dialog box to locate all conditional formats or just matching ones.
When you select the Conditional Formats option, two additional options—All and Same—become available. Selecting All is the same as using the Conditional Formatting command on the Find & Select menu to select all conditionally formatted cells and regions. If you use the Same option, however, Go To Special finds only those cells that are formatted using the same condition that exists in the selected cell. Before clicking the Go To Special command, select a cell containing the conditional format you want to locate.
The last rule type in the New Formatting Rule dialog box shown in Figure 9-26 offers the ability to create your own conditional formatting formulas. When you select the rule type labeled Use A Formula To Determine Which Cells To Format, the dialog box looks similar to the one shown in Figure 9-31.
You can create formulas to perform tasks such as identifying dates that fall on specific days of the week, specifying particular values, or doing anything you can’t quite accomplish using the built-in conditional formatting tools. For example, using our worksheet example, we typed the following formula in the Format Values Where This Formula Is True text box in the New Formatting Rule dialog box:
=IF(ISERROR(B4),0)=0
Then we clicked the Format button and selected a color on the Fill tab. The formula applies the selected fill color to any cell that generates an error value. (The cell reference B4 is the relative reference of the top-left cell of the range to which the format is applied.) When you use this technique, you can type any formula that results in the logical values TRUE (1) or FALSE (0). For example, you could use a logical formula such as =N4>AVERAGE($N$4:$N$37), which combines relative and absolute references to apply formatting to a cell when the value it contains is less than the average of the specified range. When you use relative references in this situation, the formatting formulas adjust in each cell where you apply or copy them, just as regular cell formulas do.
Note
For more information, see Using Cell References in Formulas on page 468 and Understanding Logical Functions on page 550; also see Chapter 14 and Chapter 15.