Formatting Conditionally

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.

Figure 9-20: The revamped conditional formatting features in Excel 2007 are more powerful and easier to use than previous versions.

Office Excel 2007 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) they 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 whose 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 whose 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:

1. Select the cells you want to format.

2. Click Conditional Formatting, Highlight Cells Rules, Less Than to display the dialog box shown in Figure 9-21.

3. Type the number you want to use as the threshold for this condition, in this case 1000.

4. Select one of the options from the drop-down list of available formats.

   Notice that when you select a format option in the dialog box, Excel previews it for you in the worksheet. The palette preview functionality does not work from the Conditional Formatting menu with any of the highlight cells rules or top/bottom rules, because they all require additional input first. The Custom Format option at the bottom of the With list (the drop-down list at the right side of the dialog box) does not generate a preview, because clicking it displays a version of the Format Cells dialog box with the Number, Font, Border, and Fill tabs available.

5. 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.

On the CD You'll find the  Sales by Product.xlsx file in the Sample Files section of the companion CD.

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 upon 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).

Figure 9-23: We used the same top/bottom rule on two different selections, with different results.

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 varies the size of the colored area in each cell to reflect its value relative to the other selected cells. Figure 9-24 shows a live preview of the Orange Databar.

Figure 9-24: You can rest the pointer on items on the Data Bars menu to see a live preview on your worksheet.

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-25: We used highlight cells in the body of this table and data bars in the Totals column.

Creating Conditional Formatting Rules

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.

Figure 9-26: Use the New Formatting Rule dialog box to construct your own conditional formats.

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. In Figure 9-26, we used the Format Style drop-down list to click Icon Sets and then selected 5 Quarters as the icon style. 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 coolness factor is admittedly huge using these three format styles, but you have relatively limited control over the actual formatting. You can specify colors (except for icons), but that's about it. All the other rule types give you far greater formatting flexibility. 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.

Figure 9-27: Use the second rule type in the New Formatting Rule dialog box to create your own highlight cells rules.

We discuss the last rule type, Use A Formula To Determine Which Cells To Format, in "Creating Conditional Formatting Formulas" on page 294.

Use the first drop-down list in the Edit The Rule Description area to apply a condition based on the contents of the selected cell. Besides the Cell Value option, you can choose to create a rule for highlighting Specific Text, Dates Occurring (relative to now), Blanks, No Blanks, Errors, or No Errors. The second drop-down list contains the operators Between, Not Between, Equal To, Not Equal To, Greater Than, Less Than, Greater Than Or Equal To, and Less Than Or Equal To. Then you type the comparison values in the next two text boxes. If you select Between or Not Between in the second condition drop-down list, two text boxes appear in which you provide an upper and a lower limit, as shown in Figure 9-27. Otherwise, only one text box appears.

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 are finished, 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.

Managing Conditional Formatting Rules

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 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 has been applied. If you want to change the cell range, click the Collapse button on 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.

Figure 9-29: Click the Collapse icon in the Applies To text box to minimize the dialog box and allow direct selection of the cell range you want.

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'll 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.

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.

Copying, Clearing, and Finding Conditional Formats

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 so, 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 Selected Cells or 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 have conditional formats applied to them. The Conditional Formatting command locates and selects all the cells on the current worksheet to which conditional formats have been 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, selecting all conditionally formatted cells and regions. If you use the Same option, however, Go To Special finds only those cells that have been formatted using the same condition that exists in the selected cell. Before clicking the Go To Special command, select an example cell containing the conditional format you want to locate.

Creating Conditional Formatting Formulas

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.

Figure 9-31: Use the last rule type in the list to create your own conditional formatting formulas.

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 example worksheet, 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(A3),0)=0

Then we clicked the Format button and selected the color black on the Fill tab. The formula applies the selected fill color to any cell that generates an error value. (The cell reference A3 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, as regular cell formulas do.

For more information, see "Using Cell References in Formulas" on page 428 and "Understanding Logical Functions" on page 507; also see Chapter 14, "Everyday Functions;" and Chapter 15, "Formatting and Calculating Date and Time."