Microsoft Office Tutorials and References
In Depth Information
Chapter 19: Conditional Formatting and Data Validation
It’s important to understand that color scale conditional formatting uses a gradient. For example,
if you format a range with a two-color scale, you will get a lot more than two colors because
you’ll get colors with the gradient between the two specified colors.
Figure 19-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:
Values in column A and row 1 range from 0 to 4.0, in increments of 0.04. Change the value in A1
and the colors will change instantly. The result, when viewed on your screen, is stunning. (It loses
a lot when converted to gray scale.)
You can’t hide the cell contents when using a color scale rule, so I formatted the cells
using this custom number format:
This workbook, named extreme color scale.xlsx , is available on the companion
CD-ROM. You’ll also find an animated version (which uses VBA macros), named
animated color scale.xlsm .
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 cells.
To assign an icon set to a range, select the cells and choose Home
Icon Sets. Excel provides 20 icon sets to choose from. The number of icons in the
sets ranges from 3 to 5.
Figure 19-10 shows a simple example that uses an icon set. The symbols graphically depict the
completion status of each project, based on the value in column C.
All the icon set examples in this section are available on the companion CD-ROM. The
workbook is named icon set examples.xlsx .
By default, the symbols are assigned using percentiles. For a three-symbol set, the items are
grouped into three percentiles. For a four-symbol set, they’re grouped into four percentiles. And
for a five-symbol set, the items are grouped into five percentiles.