Microsoft Office Tutorials and References
In Depth Information
I can select any of these choices, including values that fall within 1, 2, or 3 standard deviations
from the range average But the larger point is this: by selecting Edit Rule, you can basically replace .
your existing Conditional Format with any other sort of rule.
In addition, you can subject the same range to multiple rules. For example, I could compose a
Conditional Format to color blue all the cells with test scores in our range that exceed 80, and to color
red all cells with scores that fall below 60. That is, we could format our range with the Highlight Cells
Rules Greater Than… and Less Than… options. If I went ahead with this plan, my range would take on
this appearance (Figure 4–127):
Figure 4–127. Note some cells meet neither criterion and remain white
And that’s fine. But what if I wanted to color all the cells with scores topping 80 blue, and all the
cells with scores over 85 green ? Our wicket has just gotten stickier—because a score such as 90 meets
both conditions. After all, 90 exceeds both 85 and 80—raising the obvious question: which format will I
see in such a cell?
That’s a question Excel wants you to answer—because you’ll need to tell the application which of
the rules will activate first . Once you execute both rules, you’ll want to select the range and click
Manage Rules. Note (Figure 4–128):
Figure 4–128. You need to decide which rule is listed first
The two rules impacting our range are recorded—and in the proper order, because Excel will
simply carry out the rule which appears first in the above dialog box—and we want Excel to consider the
>85 rule before >80, for a simple reason. If > is listed first, then even the cell containing 90 will turn 80
blue and you’ll never get to >85. —