Excel 2010 Bible

Chapter 20: Visualizing Data Using Conditional Formatting

489

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