Microsoft Office Tutorials and References
In Depth Information
Formatting conditionally
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 on the rest of the cell values formatted using the same
condition. For example, Figure 9-23 shows the same top/bottom rule applied to two
different selected regions. (In this case, we specified the top five.)
Figure 9-23 We used the same top/bottom rule on two different selections, with different
As you can see in Figure 9-23, 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 the size of the colored area varies in each
cell to reflect the cell’s value relative to the other selected cells. Figure 9-24 shows a live
preview of the orange data bar.
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 9-25 shows what might be
considered a more judicious application of conditional formatting using highlight cells and
data bars.
Search JabSto ::

Custom Search