Microsoft Office Excel 2007 Inside Out
By Mark Dodge and Craig Stinson

Mark Dodge is a former senior technical writer for the Microsoft Office User Assistance group, and is the coauthor of four editions of Running Microsoft Excel. He was also a technical editor for over a dozen books on Microsoft applications. Mark has been honored with six awards from the Society for Technical Communication.

Craig Stinson has been an industry journalist since 1981, serving as a contributing editor of PC Magazine and author of the several editions of the best-selling Running Microsoft Windows®. In addition to being a coauthor on Running Microsoft Excel, he is the coauthor of Running Microsoft Windows NT® Workstation, Version 4. He has also written music reviews for such well-known publications as Billboard, the Boston Globe, and the Christian Science Monitor.

To learn more about other books on the 2007 Microsoft Office system, visit Microsoft Press.

This article provides an introduction to creating conditional format formulas in Microsoft Office Excel 2007.

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

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 stay “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.

  

  Figure 1  The revamped conditional formatting features in Excel 2007 are more powerful and easier to use than previous versions.
• 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 2.
3. Type the number you want to use as the threshold for this condition, in this case 1000.

   

   Figure 2  Select the Less Than rule on the Highlight Cells Rules menu to create a stoplight chart using conditional formatting.
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.

Click OK.

Figure 3 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.

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 4 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 4, 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 5 shows a live preview of the Orange Databar.

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 6 shows what might be considered a more judicious application of conditional formatting, using highlight cells and data bars.