A simple data bar

Figure 19.4 shows an 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.

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

• Show the bar only (hide the numbers).
• Specify minimum and maximum values for the scaling.
• Change the appearance of the bars.
• Specify how negative values and the axis are handled.
• Specify the direction of the bars.

Using data bars in lieu of a chart

Using the data bars conditional formatting can sometimes serve as a quick alternative to creating a chart. Figure 19.5 shows a three-column range (in B3:D14) with data bars conditional formatting to the data in column D. (Column D contains references to the values in the second column.) The conditional formatting in the third column uses the Show Bars Only option, so the values are not displayed.

Figure 19.5 also 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 may be a good option—especially when you need to create several such charts.

A color scale example

Figure 19.6 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 3-color scale, with red (in this book, the darkest gray) 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. It’s clear that the Central region consistently has lower sales volumes, but the conditional formatting doesn’t help identify monthly difference for a particular region.

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 help identify high or low sales months.

Neither one of these approach is necessarily better. The way you set up conditional formatting depends entirely on what you are trying to visualize.

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 Edit Formatting Rule dialog box, shown in Figure 19.7. Adjust the settings and watch the Preview box to see the effects of your changes.

An extreme color scale example

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 also get colors within the gradient between the two specified colors.

Figure 19.8 shows an extreme example that uses color scale conditional formatting on a range of more than 6,000 cells. The worksheet contains average daily temperatures for an 18-year period. Each row contains 365 (or 366) temperatures for the year. The columns are narrow, so the entire year can be visualized.

An icon set example

Figure 19.9 shows an example that uses an icon set. The symbols graphically depict the status of each project, based on the value in column C.

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 four 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. To modify an existing rule, choose Home ➜ Styles ➜ Conditional Formatting ➜ Manage Rules. Then select the rule to modify and click the Edit Rule button to display the Edit Formatting Rule dialog box.

Figure 19.10 shows how to modify the icon set rules such that only projects that are 100% completed get the check mark icons. Projects that are 0% completed get the X icon. All other projects get no icon.

Figure 19.11 shows the project status list after making this change.

Another icon set example

Figure 19.12 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.

This example uses the icon set named 3 Arrows, and we customized the rule using the Edit Formatting Rule dialog box:

• Up arrow: When value is ≥ 5
• Level arrow: When value < 5 and > –5
• Down arrow: When value is ≤ –5

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

In some cases, using icon sets can cause your worksheet to look cluttered. Displaying an icon for every cell in a range might result in visual overload.

Figure 19.13 shows the test results table after hiding the level arrow by choosing No Cell Icon in the Edit Formatting Rule dialog box.

Identifying weekend days

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:

=WEEKDAY(A1,2)>=6

This formula assumes that a range is selected and that cell A1 is the active cell. The WEEKDAY function’s second argument, 2 in this example, indicates that Monday returns 1 and Sunday returns 7. The default for this argument is that Sunday starts with 1, but by specifying this argument you can test that weekday is at least 6 (Saturday).

Highlighting a row based on a value

Figure 19.15 shows a worksheet that contains a conditional format in the range A3:G28. If a name entered in cell B1 is found in the first column, the entire row for that name is highlighted.

The conditional formatting formula follows:

=$A3=$B$1

Notice that a mixed reference is used for cell A3. Because the column part of the reference is absolute, the comparison is always done using the contents of column A.

Displaying alternate-row shading

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

=MOD(ROW(),2)=0

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.

Creating checkerboard shading

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)

Instead of comparing the results of MOD to 0 or 1 as in the last example, this example compares the modulo of the ROW to the modulo of the COLUMN. For odd-numbered rows, only cells in odd-numbered columns are formatted. And for even-numbered rows, only cells in even-numbered columns are formatted.

Shading groups of rows

Here’s another row 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)=1

Figure 19.17 shows an example.

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)=1

Displaying a total only when all values are entered

Figure 19.18 shows a range with a formula that uses the SUM function in cell C6. Conditional formatting is used to display the sum only when all of the four cells above are nonblank. The conditional formatting formula for cell C6 (and cell B6, which contains a label) is this:

=COUNT($C$2:$C$5)=COUNTA($B$2:$B$5)

This formula returns TRUE only if C2:C5 contains an entry for every label in B2:B5. The conditional formatting applied is a dark background color. The text color is white, so it’s legible only when the conditional formatting rule is satisfied.

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

Using custom functions in conditional formatting formulas

Excel’s conditional formatting feature is versatile, and the ability to create your own formulas to define the conditions will cover most needs. But if custom formulas still aren’t versatile enough, you can create custom VBA functions and use those in a conditional formatting formula.

This section provides three examples of VBA functions that you can use in conditional formatting formulas.

Identifying formula cells

You can use the ISFORMULA function in a conditional formatting formula to highlight all the cells in a range that contain a formula. If your workbook must be compatible with versions of Excel prior to 2013 (the version in which ISFORMULA was introduced), you can create a simple VBA function. The following custom VBA function uses the VBA HasFormula property. The function, which you can enter into a VBA module, returns TRUE if the cell (specified as its argument) contains a formula; otherwise, it returns FALSE:

Function CELLHASFORMULA(cell) As Boolean
    CELLHASFORMULA = cell.HasFormula
End Function

After you enter this function into a VBA module, you can use the function in your worksheet formulas. For example, the following formula returns TRUE if cell A1 contains a formula:

=CELLHASFORMULA(A1)

You also can use this function in a conditional formatting formula. The worksheet in Figure 19.20, for example, uses conditional formatting to identify cells that contain a formula. In this case, formula cells display a background color.

Function CELLHASDATE(cell) As Boolean
    CELLHASDATE = IsDate(cell)
End Function

=AND(CELLHASDATE(A1),MONTH(A1)=6)

=AND(CELLHASDATE(A1), WEEKDAY(A1,2)>=6)

Identifying invalid data

You might have a situation in which the data entered must adhere to some specific rules, and you’d like to apply special formatting if the data entered is not valid. For example, consider part numbers that consist of seven characters: four uppercase alphabetic characters, followed by a hyphen, and then a two-digit number—for example, ADSS-09 or DYUU-43.

You can write a conditional formatting formula to determine whether part numbers adhere to this structure, but the formula is complex. The following formula, for example, returns TRUE only if the value in A1 meets the part number rules specified:

=AND(LEN(A1)=7,AND(LEFT(A1)>="A",LEFT(A1)<="Z"),
AND(MID(A1,2,1)>="A",MID(A1,2,1)<="Z"),AND(MID(A1,3,1)>="A",
MID(A1,3,1)<="Z"),AND(MID(A1,4,1)>="A",MID(A1,4,1)<="Z"),
MID(A1,5,1)="-",AND(VALUE(MID(A1,6,2))>=0,
VALUE(MID(A1,6,2))<=99))

For a simpler approach, write a custom VBA worksheet function. The VBA Like operator makes this sort of comparison relatively easy. The following VBA function procedure returns TRUE if its argument does not correspond to the part number rules outlined previously:

Function INVALIDPART(Part) As Boolean
    If Part Like "[A-Z][A-Z][A-Z][A-Z]-##" Then
       INVALIDPART = False
    Else
       INVALIDPART = True
    End If
End Function

After defining this function in a VBA module, you can enter the following conditional formatting formula to apply special formatting if cell A1 contains an invalid part number:

=INVALIDPART(A1)

Figure 19.21 shows a range that uses the custom INVALIDPART function in a conditional formatting formula. Cells that contain invalid part numbers have a colored background.

In many cases, you can simply take advantage of Excel’s data validation feature, which is described next.