Microsoft Office Tutorials and References
In Depth Information
The next formatting option in the Styles group—really a whole warehouse of them—is an interesting
and important one: Conditional Formatting . Conditional Formatting enables the user to define a
condition that a cell must meet—and if the condition is met, the cell experiences a change in its
If that sounds abstract, here are some simple for instances. I can devise conditional formats which
state the following: If any number in a range of cells exceeds 50, let those cell turn green, as a way of
calling attention to that cell. Or, if I select a range of test scores, let those cells containing scores in the
lowest 20 percent turn red.
Is any of this starting to sound—or look—vaguely familiar? It might, because if we go all the way
back to Chapter 1 and that introductory grade sheet (shown in Figure 4–103):
Figure 4–103. The Gradebook, exhibiting conditional formats
You’ l l r e ca l l tha t the ce l l s con ta i n i n g the hi g he st scor e s i n e a ch e xa m we r e col or e d a con spi cuous
red—and that effect was brought about by a conditional format.
Excel has tried to make conditional formatting easy to apply—and for the most part, it’s succeeded.
Once you get a handle on the basic concept, you’ll see that the wide array of Conditional Formatting
options available to you work in similar ways, making the process pretty painless—and valuable.
Let’s try a Conditional Format and you’ll see what I mean. Instead of identifying the highest
scorers on an exam, say you want to determine which students have scored below 65, which may
represent the passing grade. On a blank spreadsheet, let’s copy the above results for the exam 1 in
cells B8:C18, minus the formatting (Figure 4–104):