Microsoft Office Tutorials and References
In Depth Information
Chapter 19: Conditional Formatting and Data Validation
h Format Only Unique or Duplicate Values: Use this rule type to create rules that format
unique or duplicate values in a range.
h Use a Formula to Determine Which Cells to Format: Use this rule type to create rules
based on a logical formula. See the section “Creating formula-based rules,” later in this
Conditional formats that use graphics
This section describes the three conditional formatting options that display graphics: data bars,
color scales, and icon sets. These types of conditional formatting can be useful for visualizing the
values in a range.
Using data bars
The data bars conditional format displays horizontal bars directly in the cell. The length of the bar
is based on the value of the cell, relative to the other values in the range.
The data bars feature has been improved significantly in Excel 2010. Data bars now
display proportionally (just like a bar chart), and there is now an option to display data
bars in a solid color (no color gradient) and with a border. In addition, your negative
values can now display in a different color, and to the left of an axis.
Figure 19-4 shows a simple example of data bars. It’s a list of tracks on Bob Dylan albums, with
the length of each track in column D. I applied data bar conditional formatting to the values in
column D. You can tell at a glance which tracks are longer.
When you adjust the column width, the bar lengths adjust accordingly. The differences
among the bar lengths are more prominent when the column is wider.
Excel provides quick access to 12 data bar colors via the Home
Data Bars command. For additional choices, click the More Rules option, which displays the New
Formatting Rule dialog box. Use this dialog box to
h Show the bar only (hide the numbers)
h Specify Minimum and Maximum values for the scaling
h Change the appearance of the bars
h Specify how negative values on the axis are handled
h Specify the direction of the bars
If you make adjustments in this dialog box, you can use the Preview button to see the formats
before you commit to them by clicking OK.