Chapter 5: Formatting Worksheets
55
■ (^) Specify how negative values and the axis are handled.
■ Specify the direction of the bars.
Oddly, if you add data bars using one of the 12 data bar styles, the colors used for data bars are not theme colors. If
you apply a new document theme, the data bar colors do not change. However, if you add the data bars by using the
New Formatting Rule dialog box, the colors you choose are theme colors.
Using color scales
The color scale conditional formatting option varies the background color of a cell based on
the cell’s value relative to other cells in the range.
Figure 5.12 shows examples of color scale conditional formatting. The example on the left
depicts monthly sales for three regions. Conditional formatting was applied to the range
B4:D15. The conditional formatting uses a three-color scale, with red for the lowest value, yel-
low for the midpoint, and green for the highest value. Values in between are displayed using a
color within the gradient. It’s clear that the Central region consistently has lower sales volumes,
but the conditional formatting doesn’t help identify monthly differences for a particular region.
FIGURE 5.12
Two examples of color scale conditional formatting
The example on the right shows the same data, but conditional formatting was applied to each
region separately. This approach facilitates comparisons within a region and can identify high
or low sales months.
Neither one of these approaches is necessarily better. The way you set up conditional format-
ting depends entirely on what you’re trying to visualize.