Figure 21.4 shows a simple example of data bars. It’s a list of customers and sales amounts. I applied data bar conditional formatting to the values in column B. You can tell at a glance where the higher values are.

Figure 21.4. The length of the data bars is proportional to the value in the cell.

Excel provides quick access to six data bar colors via the Home Styles Conditional Formatting Data Bars command. For additional choices, click the More Rules option, which displays the New Formatting Rule dialog box. Use this dialog box to:

If you make adjustments in this dialog box, you can use the Preview button to see the formats before you commit to them with the OK button.

Range("B2:B123").FormatConditions(1).PercentMin = 1

Using the data bars conditional formatting can sometimes serve as a quick alternative to creating a chart. Figure 21.5 shows a three-column table of data, with data bars applied in the third column. The third column of the table contains references to the values in the second column. The conditional formatting in the third column uses the Show Bars Only option.

Figure 21.5. This table uses data bars conditional formatting.

Figure 21.6 shows an actual bar chart created from the same data. The bar chart takes about the same amount of time to create and is a lot more flexible. But for a quick and dirty chart, data bars are a good option—especially when you need to create several such charts.

Figure 21.6. A real Excel bar chart (not conditional formatting data bars).

Figure 21.7 shows a range of cells that use color scale conditional formatting. It depicts the number of employees on each day of the year. This is a 3-color scale that uses red for the lowest value, yellow for the midpoint, and green for the highest value. Values in between are displayed using a color within the gradient.

Figure 21.7. A range that uses color scale conditional formatting.

Excel provides four 2-color scale presets and four 3-color scales presets, which you can apply to the selected range by choosing Home Styles Conditional Formatting Color Scales.

To customize the colors and other options, choose Home Styles Conditional Formatting Color Scales More Rules. This command displays the New Formatting Rule dialog box, shown in Figure 21.8.

Figure 21.8. Use the New Formatting Rule dialog box to customize a color scale.

It’s important to understand that color scale conditional formatting uses a gradient. For example, if you format a range using a 2-color scale, you will get a lot more than two colors. You’ll get colors with the gradient between the two specified colors.

Figure 21.9 shows an extreme example that uses color scale conditional formatting on a range of 10,000 cells (100 rows X 100 columns). The worksheet is zoomed down to 20% to display a very smooth 3-color gradient. The range contains formulas like this one, in cell C5:

=SIN($A2)+COS(B$1)Values in column A and row 1 range from 0 to 4.0, in increments of 0.04.

Figure 21.9. This worksheet, which uses color scale conditional formatting, is zoomed to 20%.

When viewed on your screen, the result is stunning (it loses a lot when converted to greyscale).

;;;

Figure 21.10 shows a simple example that uses the icon set named Three Symbols (Uncircled). The symbols graphically depict the status of each project, based on the value in column C.

Figure 21.10. Using an icon set to indicate the status of projects.

By default, the symbols are assigned using percentiles. For a 3-symbol set, the items are grouped into three percentiles. For a 4-symbol set, they’re grouped into 4 percentiles. And for a 5-symbol set, the items are grouped into five percentiles.

If you would like more control over how the icons are assigned, choose Home Styles Conditional Formatting Icon Sets More Rules to display the New Formatting Rule dialog box. Figure 21.11 shows how to modify the icon set rules such that only projects that are 100% completed get the check mark icons. Projects which are 0% completed get the X icon. All other projects get the exclamation point icon.

Figure 21.11. Changing the icon assignment rule.

Figure 21.12 shows the task list after making this change.

Figure 21.12. Using a customized icon set to indicate the status of projects.

Figure 21.13 shows a table that contains two test scores for each student. The Change column contains a formula that calculates the difference between the two tests. The Trend column uses an icon set to display the trend graphically.

Figure 21.13. The arrows depict the trend from Test 1 to Test 2.

This example uses the icon set named 3 Arrows, and I customized the rule:

In other words, difference of five points or less in either direction is considered an even trend. An improvement of more than five points is considered a positive trend, and a decline of more than five points is considered a negative trend.

In some cases, you may want to display only one icon from an icon set. Excel doesn’t provide this option directly, but displaying a single icon is possible if you use two rules. Figure 21.14 shows a range of values. Only the values greater than or equal to 80 display an icon.

Figure 21.14. It’s possible to display only one icon.

Here’s how to set up an icon set such that only values greater than or equal to 80 display an icon:

The first rule checks to see whether the value is less than 80. If so, rule checking stops, and no conditional formatting is applied. If the value is greater than or equal to 80, the second rule kicks in. This rule indicates that values greater than or equal to 80 are displayed with an icon.

Excel provides a number of conditional formatting rules that deal with dates, but it doesn’t let you identify dates that fall on a weekend. Use this formula to identify weekend dates:

=OR(WEEKDAY(A1)=7,WEEKDAY(A1)=1)

This formula assumes that a range is selected and that cell A1 is the active cell.

The conditional formatting formula that follows was applied to the range A1:D18, as shown in Figure 21.17, to apply shading to alternate rows.

=MOD(ROW(),2)=0

Figure 21.17. Using conditional formatting to apply formatting to alternate rows.

Alternate row shading can make your spreadsheets easier to read. If you add or delete rows within the conditional formatting area, the shading is updated automatically.

This formula uses the ROW function (which returns the row number) and the MOD function (which returns the remainder of its first argument divided by its second argument). For cells in even-numbered rows, the MOD function returns 0, and cells in that row are formatted.

For alternate shading of columns, use the COLUMN function instead of the ROW function.

The following formula is a variation on the example in the preceding section. It applies formatting to alternate rows and columns, creating a checkerboard effect.

=MOD(ROW(),2)=MOD(COLUMN(),2)

Here’s another rows shading variation. The following formula shades alternate groups of rows. It produces four rows of shaded rows, followed by four rows of unshaded rows, followed by four more shaded rows, and so on.

=MOD(INT((ROW()-1)/4)+1,2)

Figure 21.18 shows an example.

Figure 21.18. Conditional formatting produces these groups of alternate shaded rows.

For different sized groups, change the 4 to some other value. For example, use this formula to shade alternate groups of two rows:

=MOD(INT((ROW()-1)/2)+1,2)

Figure 21.19 shows a range with a formula that uses the SUM function in cell C6. Conditional formatting is used to hide the sum if any of the four cells above is blank. The conditional formatting formula for cell C6 (and cell C5, which contains a label) is

=COUNT($C$2:$C$5)=4

Figure 21.19. The sum is displayed only when all four values have been entered.

This formula returns TRUE only if C2:C5 contains no empty cells.

Figure 21.20 shows the worksheet when one of the values is missing.

Figure 21.20. A missing value causes the sum to be hidden.