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