Microsoft Office Tutorials and References
In Depth Information
Chapter 19: Conditional Formatting and Data Validation
Excel 2010 improvements
If you’ve used conditional formatting in Excel 2007, you’ll find several improvements in Excel
Data bars display proportionally, and there is now an option to display data bars in a solid
color (no color gradient) and with a border.
Data bars handle negative values much better.
Data bars use theme colors, so if you apply a new document theme, the color changes.
You can specify minimum and maximum values for data bars.
Users can now create customized icon sets.
It’s easy to hide one or more icons in an icon set.
Formatting types you can apply
When you select a conditional formatting rule, Excel displays a dialog box that’s specific to that
rule. These dialog boxes have one thing in common: a drop-down list with common formatting
Figure 19-2 shows the dialog box that appears when you choose Home
Between. This particular rule applies the formatting if the
value in the cell falls between two specified values. In this case, you enter the two values (or
enter cell references) and then use the drop-down control to choose the type of formatting to
display if the condition is met.
Highlight Cells Rules
Figure 19-2: One of several different conditional formatting dialog boxes.
The formatting suggestions in the drop-down control are just a few of thousands of different
formatting combinations. In most cases, none of Excel’s suggestions are what you want, so you
choose the Custom Format option to display the Format Cells dialog box. You can specify the
format in any or all of the four tabs: Number, Font, Border, and Fill.
The Format Cells dialog box used for conditional formatting is a modified version of the
standard Format Cells dialog box. It doesn’t have the Alignment and Protection tabs,
and some of the Font formatting options are disabled. The dialog box also includes a
Clear button that clears any formatting already selected.