CHAPTER

Visualizing Data

Using Conditional

Formatting

481

Part III: Creating Charts and Graphics

•Duplicate values: Values that appear more than one time are highlighted.

•Words that contain X: If the cell contains X (upperor lowercase), the cell is highlighted.

•Data bars: Each cell displays a horizontal bar, proportional to its value.

•Color Scale: The background color varies, depending on the value of the cells. You can choose from several different color scales or create your own.

•Icon Set: One of several icon sets. It displays a small graphic in the cell. The graphic varies, depending on the cell value.

•Icon Set: Another icon set, with all but one icon hidden.

•Custom rule: The rule for this checkerboard pattern is based on a formula:

=MOD(ROW(),2)=MOD(COLUMN(),2)

On the CD

This workbook, named conditional formatting examples.xlsx, is available on the companion CD-ROM. n

FIGURE 20.1

This worksheet demonstrates a few conditional formatting rules.

482

Chapter 20: Visualizing Data Using Conditional Formatting

Specifying Conditional Formatting

To apply a conditional formatting rule to a cell or range, select the cells and then use one of the commands from the Home Styles Conditional Formatting drop-down list to specify a rule. The choices are

•Highlight Cell Rules: Examples rules include highlighting cells that are greater than a particular value, between two values, contain specific text string, a date, or are duplicated.

•Top Bottom Rules: Examples include highlighting the top 10 items, the items in the bottom 20 percent, and items that are above average.

•Data Bars: Applies graphic bars directly in the cells, proportional to the cell’s value.

•Color Scales: Applies background color, proportional to the cell’s value.

•Icon Sets: Displays icons directly in the cells. The icons depend on the cell’s value.

•New Rule: Enables you to specify other conditional formatting rules, including rules based on a logical formula.

•Clear Rules: Deletes all the conditional formatting rules from the selected cells.

•Manage Rules: Displays the Conditional Formatting Rules Manager dialog box, in which you create new conditional formatting rules, edit rules, or delete rules.

Formatting types you can apply

When you select a conditional formatting rule, Excel displays a dialog box specific to that rule. These dialog boxes have one thing in a common: a drop-down list with common formatting suggestions.

Figure 20.2 shows the dialog box that appears when you choose Home Styles Conditional Formatting Highlight Cells Rules Between. This particular rule applies the formatting if the value in the cell falls between two specified values. In this case, you enter the two values (or specify cell references), and then use choices from the drop-down list to set the type of formatting to display if the condition is met.

FIGURE 20.2

One of several different conditional formatting dialog boxes.

483

Part III: Creating Charts and Graphics

Excel 2010 Improvements

If you’ve used conditional formatting in Excel 2007, you’ll find several improvements in Excel 2010:

•Data bars display proportionally.

•Data bars can display in a solid color with a border. Previously, data bars always displayed with a gradient.

•Data bars handle negative values much better.

•You can specify minimum and maximum values for data bars.

•You can create customized Icon sets.

•Hiding one or more icons in an icon set is easy.

The formatting suggestions in the drop-down list are just a few of thousands of different formatting combinations. If none of Excel’s suggestions are what you want, choose the Custom Format option to display the Format Cells dialog box. You can specify the format in any or all of the four tabs:

Number, Font, Border, and Fill.

Note

The Format Cells dialog box used for conditional formatting is a modified version of the standard Format Cells dialog box. It doesn’t have the Alignment and Protection tabs, and some of the Font formatting options are disabled. The dialog box also includes a Clear button that clears any formatting already selected. n

Making your own rules

For do-it-yourself types, Excel provides the New Formatting Rule dialog box, shown in Figure 20.3. Access this dialog box by choosing Home Styles Conditional Formatting New Rules.

Use the New Formatting Rule dialog box to re-create all the conditional format rules available via the Ribbon, as well as new rules. First, select a general rule type from the list at the top of the dialog box. The bottom part of the dialog box varies, depending on your selection at the top. After you specify the rule, click the Format button to specify the type of formatting to apply if the condition is met. An exception is the first rule type, which doesn’t have a Format button (it uses graphics rather than cell formatting).

Here is a summary of the rule types:

•Format all cells based on their values: Use this rule type to create rules that display data bars, color scales, or icon sets.

•Format only cells that contain: Use this rule type to create rules that format cells based on mathematical comparisons (greater than, less than, greater than or equal to, less than or equal to, equal to, not equal to, between, not between). You can also create rules based

484