Microsoft Office Tutorials and References
In Depth Information
Advanced Conditional Formatting
Starting in Excel 2010 and Mac Excel 2011, the Conditional Formatting has
negative bars for negative values. These bars did not exist in 2007. See Figure 5.10.
Color Scales lets you detect directions, changes, or areas of specific values in the
data set. Once more, Excel applies the conditional formatting to the entire region
based on the lowest and highest values in that range. The background colors change
according to their values. See Figure 5.11. Now note how in the right two columns,
the left range (from
10) take the format of the 0 range on its right.
You will find the Colors Scales very useful when you try to detect high or low
values in an optimization problem. To illustrate this concept I created an area in
Excel that calculates a minimum based on the Pythagoraean theorem (it will be part
of the case study in Chapter 36). The calculated spreadsheet area and the chart, using
the data, are shown in Figure 5.12. You will see later in the chapter that you can
change the preset rules with different ranges.
Using the Colors Scales can, based on the values, illuminate the point(s) of
minimum based on the color intensity. See Figure 5.13. Color Scales clearly call
attention to where the minimum or lower values are. Note how the lower values that
are lighter on the range correspond to the lower area on the chart in Figure 5.13.
The last option of the newer version of Conditional Formatting is the Icon Sets
option. Figure 5.14 displays all the Icon Sets for Excel 2010/11. The circled ones are
new for 2010/11 and did not appear in Excel 2007.
FIGURE 5.10 New Negative Bar Option for Excel 2010 and MAC Excel 2011
FIGURE 5.11 Color Scales Are Based on the Region
s Formatted Range of Values