Chapter 20: Visualizing Data Using Conditional Formatting

on text, dates, blanks, nonblanks, and errors. This rule type is very similar to how conditional formatting was set up in previous versions of Excel.

•Format only top or bottom ranked values: Use this rule type to create rules that involve identifying cells in the top n, top n percent, bottom n, and bottom n percent.

•Format only values that are above or below average: Use this rule type to create rules that identify cells that are above average, below average, or within a specified standard deviation from the average.

•Format only unique or duplicate values: Use this rule type to create rules that format unique or duplicate values in a range.

•Use a formula to determine which cells to format: Use this rule type to create rules based on a logical formula. See “Formula-Based Conditions,” later in this chapter.

FIGURE 20.3

Use the New Formatting Rule dialog box to create your own conditional formatting rules.

Conditional Formats That Use Graphics

This section describes the three conditional formatting options that display graphics: data bars, color scales, and icons sets. These types of conditional formatting can be useful for visualizing the values in a range.

Using data bars

The data bars conditional format displays horizontal bars directly in the cell. The length of the bar is based on the value of the cell, relative to the other values in the range.

485

Part III: Creating Charts and Graphics

New Feature

The data bars feature is improved significantly in Excel 2010. Data bars now display proportionally (just like a bar chart), and there is now an option to display data bars in a solid color (no more forced color gradient) and with a border. In addition, negative values can now display in a different color, and to the left of an axis. n

A simple data bar

Figure 20.4 shows an example of data bars. It’s a list of tracks on Bob Dylan albums, with the length of each track in column D. I applied data bar conditional formatting to the values in column D. You can tell at a glance which tracks are longer.

On the CD

The examples in the section are available on the companion CD-ROM. The workbook is named data bars examples.xlsx.

FIGURE 20.4

The length of the data bars is proportional to the track length in the cell in column D.

Tip

When you adjust the column width, the bar lengths adjust accordingly. The differences among the bar lengths are more prominent when the column is wider. n

Excel provides quick access to 12 data bar styles via Home Styles Conditional Formatting Data Bars. For additional choices, click the More Rules option, which displays the New Formatting Rule dialog box. Use this dialog box to

486

Chapter 20: Visualizing Data Using Conditional Formatting

•Show the bar only (hide the numbers).

•Specify Minimum and Maximum values for the scaling.

•Change the appearance of the bars.

•Specify how negative values and the axis is handled.

•Specify the direction of the bars.

Note

Oddly, the colors used for data bars are not theme colors. If you apply a new document theme, the data bar colors do not change. n

Using data bars in lieu of a chart

Using the data bars conditional formatting can sometimes serve as a quick alternative to creating a chart. Figure 20.5 shows a three-column table of data (created by using Insert Tables Table), with data bars conditional formatting applied in the third column. The third column of the table contains references to the values in the second column. The conditional formatting in the third column uses the Show Bars Only option, so the values are not displayed.

FIGURE 20.5

This table uses data bars conditional formatting.

Figure 20.6 shows an actual bar chart created from the same data. The bar chart takes about the same amount of time to create and is a lot more flexible. But for a quick-and-dirty chart, data bars are a good option — especially when you need to create several such charts.

Using color scales

The color scale conditional formatting option varies the background color of a cell based on the cell’s value, relative to other cells in the range.

487

Part III: Creating Charts and Graphics

FIGURE 20.6

A real Excel bar chart (not conditional formatting data bars).

A color scale example

Figure 20.7 shows a range of cells that use color scale conditional formatting. It depicts the number of employees on each day of the year. This is a 3-color scale that uses red for the lowest value, yellow for the midpoint, and green for the highest value. Values in between are displayed using a color within the gradient.

FIGURE 20.7

A range that uses color scale conditional formatting.

On the CD

This workbook, named color scale example.xlsx, is available on the companion CD-ROM. n

488

Chapter 20: Visualizing Data Using Conditional Formatting

Excel provides four 2-color scale presets and four 3-color scales presets, which you can apply to the selected range by choosing Home Styles Conditional Formatting Color Scales.

To customize the colors and other options, choose Home Styles Conditional Formatting Color Scales More Rules. This command displays the New Formatting Rule dialog box, shown in Figure 20.8. Adjust the settings, and watch the Preview box to see the effects of your changes.

FIGURE 20.8

Use the New Formatting Rule dialog box to customize a color scale.

An extreme color scale example

It’s important to understand that color scale conditional formatting uses a gradient. For example, if you format a range using a 2-color scale, you will get a lot more than two colors. You’ll get colors with the gradient between the two specified colors.

Figure 20.9 shows an extreme example that uses color scale conditional formatting on a range of 10,000 cells (100 rows x 100 columns). The worksheet is zoomed down to 20% to display a very smooth three-color gradient. The range contains formulas like this one, in cell C5:

=SIN($A2)+COS(B$1)

Values in column A and row 1 range from 0 to 4.0, in increments of 0.04.

When viewed onscreen, the result is stunning; it loses a lot when converted to grayscale.

On the CD

This workbook, named extreme color scale.xlsx, is available on the companion CD-ROM. n

489

Part III: Creating Charts and Graphics

FIGURE 20.9

This worksheet, which uses color scale conditional formatting, displays an impressive color gradient.

Note

You can’t hide the cell contents when using a color scale rule, so I formatted the cells using this custom number format (which effectively hides the cell content):

;;;

Using icon sets

Yet another conditional formatting option is to display an icon in the cell. The icon displayed depends on the value of the cell.

To assign an icon set to a range, select the cells and choose Home Styles Conditional Formatting Icon Sets. Excel provides 20 icon sets to choose from. The number of icons in the sets ranges from three to five. You cannot supply your own icons.

490

Chapter 20: Visualizing Data Using Conditional Formatting

An icon set example

Figure 20.10 shows an example that uses an icon set. The symbols graphically depict the status of each project, based on the value in column C.

On the CD

All the icon set examples in this section are available on the companion CD-ROM. The workbook is named icon set examples.xlsx.

FIGURE 20.10

Using an icon set to indicate the status of projects.

By default, the symbols are assigned using percentiles. For a 3-symbol set, the items are grouped into three percentiles. For a 4-symbol set, they’re grouped into four percentiles. And for a 5-symbol set, the items are grouped into five percentiles.

If you would like more control over how the icons are assigned, choose Home Styles Conditional Formatting Icon Sets More Rules to display the New Formatting Rule dialog box. To modify an existing rule, choose Home Styles Conditional Formatting Manage Rules. Then select the rule to modify and click the Edit Rule button.

Figure 20.11 shows how to modify the icon set rules such that only projects that are 100% completed get the check mark icons. Projects that are 0% completed get the X icon. All other projects get no icon.

Figure 20.12 shows project status list after making this change.

491

Part III: Creating Charts and Graphics

FIGURE 20.11

Changing the icon assignment rule.

FIGURE 20.12

Using a modified rule and eliminating an icon makes the table more readable.

Another icon set example

Figure 20.13 shows a table that contains two test scores for each student. The Change column contains a formula that calculates the difference between the two tests. The Trend column uses an icon set to display the trend graphically.

This example uses the icon set named 3 Arrows, and I customized the rule:

492

Chapter 20: Visualizing Data Using Conditional Formatting

•Up Arrow: When value is >= 5

•Level Arrow: When value < 5 and > –5

•Down Arrow: When value is <= –5

In other words, a difference of no more than five points in either direction is considered an even trend. An improvement of at least five points is considered a positive trend, and a decline of five points or more is considered a negative trend.

Note

The Trend column contains a formula that references the Change column. I used the Show Icon Only option in the Trend column, which also centers the icon in the column. n

FIGURE 20.13

The arrows depict the trend from Test 1 to Test 2.

In some cases, using icon sets can cause your worksheet to look very cluttered. Displaying an icon for every cell in a range might result in visual overload.

Figure 20.14 shows the test results table after hiding the level arrow by choosing No Cell Icon in the Edit Formatting Rule dialog box.

493