Conditional Formatting

This page describes the Conditional Formatting tool. This powerful tool was added to Excel in the Excel97 version. It is not available in earlier versions.

What Is Conditional Formatting?

Conditional Formatting (CF) is a tool that allows you to apply formats to a cell or range of cells, and have that formatting change depending on the value of the cell or the value of a formula. For example, you can have a cell appear bold only when the value of the cell is greater than 100. When the value of the cell meets the format condition, the format you select is applied to the cell. If the value of the cell does not meet the format condition, the cell's default formatting is used. (By "default formatting", I mean the formatting that you set up using the normal formatting tools, not necessarily the worksheet's default font and font size.)

A cell can have up to 3 format conditions, each with its own formats, in addition to the default value of "no formatting". This allows you to have different formats depending on the value of the cell. For example, if the value was greater than 200, you can display the text in red, but if the value is between 100 and 200, display the text in green.

Remember that Conditional Formatting is the same as adding one or more formulas to each cell in which you use it, so applying Conditional Formatting to a large number of cells may cause performance degradations. Use caution when applying to to large ranges.

Simple Conditional Formatting

The simplest Conditional Formatting uses the Cell Value Is option in the CF dialog box, and uses one of the preset comparison operations. This CF Dialog for Excel2000 is shown below.

This dialog shows a format condition that will display the cell in Red when the value of the cell is between 10 and 20. In addition to the between operation, there are several other comparison operations like greater than and less than.

To apply a format condition to a cell or range of cells, first select the range to which you want to apply the format condition, then open the CF dialog from the Format menu. This displays the dialog shown above. Next, change the between operation to which ever operation you want. Next, enter the value or values for that condition. Finally, click the Format button on the dialog box. You'll see the standard cell formatting dialog. Not all format items are available in Conditional Formatting. For example, you cannot change the Font or Font Size with Conditional Formatting. Once you have select your format, click the OK button.

You can add a second or third format condition by clicking the "Add>>" button on the dialog. Each of the three format conditions can have its own format style.

Order Of Conditions

When you have more than one format condition for a cell, only the first format condition which is true is used. The remaining conditions are not evaluated. For example, suppose you have three format conditions for cell A1.

1) Bold Text when the value is greater than 10
2) Red Text when the value is greater than 20
3) Gray Background when the value is greater than 30

In this case, if the value of A1 is 100, the text will display in bold, but not red or with a gray background, because one the first condition, greater than 10, is met, the remaining conditions are not evaluated. To get around this, you must put your format conditions in the right order.

1) Gray Background when the value is greater than 30
2) Red Text when the value is greater than 20
3) Bold Text when the value is greater than 10

Here, the most restrictive condition is entered first, and the least restrictive condition is entered last. In this example, A1 will appear with a gray background if the value is greater than 30, with red text if the value is between 21 and 30, with bold text if the value is between 11 and 20, and in the default format if the value is between 0 and 10.

Conditions are never combined. This means that in the example above, a value of 40 will appear in with a gray background (from Condition 1), but not with red text (Condition 2) or in bold text (Condition 3). Even though all three conditions are true, logically, format conditions are not evaluated once a true conditions is found.

The logic of Conditional Formatting can be described as

If Condition1 = True Then
    Apply Format1
Else
    If Condition2 = True Then
        Apply Format2
    Else
        If Condition3 = True Then
            Apply Format3
        Else
            Apply DefaultFormat
        End If
    End If
End If

The logic of Conditional Formatting is NOT

If Condition1 = True Then
    Apply Format1
End If
If Condition2 = True Then
    Apply Format2
End If
If Condition3 = True Then
    Apply Format3
End If

It is important to understand the distinction between these two logical structures.

Using Formulas In Conditional Formatting

In addition to using the built in comparison operations from the Cell Value Is option, you can use your own custom formula to determine whether the format condition should be applied. To use a custom formula in the format condition, change Cell Value Is to Formula Is in the CF dialog, and enter you formula in the text box that appears. You formula should return a value of either True (non-zero) or False (zero). If your formula returns True, that format condition is applied. If the formula returns False, the format condition is not applied, and the next (if any) format condition is tested.

An advantage of using a custom formula in the format condition is that it allows you to change the format of one cell based on the value of another cell. For example, if you want A1 to appear in red if cell B1 is greater than 10, you can use the formula =IF(B1>10,TRUE,FALSE) , or, more simply, =B1>10 , as the custom formula. You can use any standard Excel worksheet formula, with the following exceptions:

The formula cannot reference a range in another worksheet or workbook (but see below for a way to get around this)
You cannot use functions in an Add-In module. But you can call the function from a VBA function in the same workbook and return the result by calling your VBA function from your formula.

Absolute And Relative References In Format Conditions

When you use custom formulas in Conditional Formatting, you need to be aware of the differences between absolute and relative references. If you use CF to apply format conditions to a range of cells, any relative addresses will be translated as Excel adds the format conditions for all the cells. For example, suppose we want to apply format conditions to A1:A10 to display the cell in bold if the value in B1:B10 is greater than 10. We can use the formula =B1>10 to accomplish this. As Excel applies the Conditional Formatting to each cell in A1:A10, it will change the B1 in the formula to the proper cell value. The format condition in A7 will be =B7>10. This is generally what we would want. However, suppose we want to A1:A10 to be bold if the value in B1 was greater than 10. I.e., each cell in A1:A10 is always compared to B1. For this, we would use the formula =$B$1>10, which will not be translated as Conditional Formatting is applied to each cell in A1:A10. The format condition in A7 would remain =$B$1>10.

Array Formulas In Format Conditions

Conditional Formatting evaluates custom formulas as though they were array formula, so you may use array formulas in format conditions. You do not enter them with Ctrl+Shift+Enter in the CF dialog as you normally do in worksheet cells. Excel will always treat a custom formulas in CF as an array formula, even if it is not one.

Using Defined Names In Conditional Formatting

As noted above, custom functions in Conditional Formatting cannot reference cells in other worksheets in the same workbook, and cannot reference cells in other workbooks. However, you can get around this limitation by using defined names. Create a defined name which refers to the list in the other workbook or worksheet, and then use that name in your custom function.

For example, suppose you want to make cell A1 on Sheet1 red if that cell's entry is not found on a list on Sheet2, cells B1:B10. If you tried to use the formula =COUNTIF(Sheet2!$B$1:$B$10,A1)=0 as your formula, you would receive an error message from Conditional Formatting. To get around this error, create a defined name called MyList which refers to the range =Sheet2!$B$1:$B$10 and use the name in your custom formula:

=COUNTIF(MyList,A1)=0

Using Conditional Formatting To Shade Rows

You can use the Conditional Formatting tool in Excel97 and 2000 to make your worksheets look like accounting ledgers or computer "green bar" paper, with alternating bands of colors. By using Conditional Formatting rather than manually formatting the cells, the color bars will remain intact after you sort a worksheet range. Read Color Banding With Conditional Formatting for more details.

Determining Which Format Condition Is In Effect

Excel does not give you a direct way to determine whether conditional formatting is currently in effect for a cell. You must use VBA to actually test the defined conditions. See the Conditional Formatting Colors page for more details.