UGTS Document #62 - Last Modified: 12/27/2015 2:29 PM
Excel Conditional Formatting
Conditional formatting in Excel can be quite powerful, and often can be used in place of all explicitly applied
cell formatting rules. If the rules are defined well, then all you need to maintain in a spreadsheet is
the content, and the formatting will mostly update itself accordingly.
Each worksheet in a workbook has an ordered list of conditional formatting rules. Each rule has the following attributes:
- Scope - which cells does this rule apply to?
Scope is a comma separated list of ranges. Each range in the list can be a single cell, row, column,
or other two dimension range of cells, using the standard range notation. However, note that 3-D ranges are not supported,
and if you try to type in a range belonging to a worksheet other than the one you are viewing, that range will be discarded.
There also is no apparent way to copy rules between worksheets. This can be a serious limitation if your Excel file contains a large number of similar worksheets which all need the same
set of formatting rules.
The ability to select multiple disjoint ranges on the same sheet can help when
you need the rule to apply to disjoint sets of columns within a table.
- Condition - which condition must be true to apply the rule to the cell?
Condition can be one of several types, but the most powerful condition type is the Formula. In a Formula
Condition, you enter an expression involving relative or absolute cell references which is evaluated
for each cell to get a true (rule applies to the cell) or false (rule does not apply) value. This
article explains how relative references can be used quite powerfully in a formula to format a whole range of cells
intelligently with a single rule. One common use for relative
references is to format an entire row based on the value of a particular
column in that row, to indicate exceptional rows. Here's an
article of typical uses for conditional formatting.
Formula expressions can also use all of the built-in Excel functions, and any User Defined VBA functions (UDF) that you create.
One common use of a custom VBA function
here is to define an IsFormula function taking a Range as input. Then
you can call this function to apply conditional formatting to all cells
containing formulas (thus giving you a visual cue that you should not
manually edit the contents of these cells, and also showing you
automatically if any cells have their formulas erroneously replaced with
Note that you cannot use structure references in a
formula or a scope. Only standard relative and absolute references are currently
supported because of the way in which relative references are inferred for
every cell in the range that the rule applies.
- Format - what format should be used if the rule applies to the cell?
All of the standard formatting optional are available - font size/type/weight, color, background color and fill pattern,
borders, and value formatting.
- Stop - if the rule applied to the cell, should additional rules be processed or not?
If you use conditional formatting, you should be aware of the following limitations not already mentioned:
- Relative References In Formulas - If your formatting condition is a formula, and the formula references
an adjacent cell by a relative reference, you need to be careful how the formula is written. If you do it in the simplest
and direct way, such as =$A1=$A2 to compare a cell's contents to the cell immediately below it in column A, then the rule
will be fragile. If you later try to insert a row in the middle of the range, Excel will take your conditional formatting rule
and split its range into 2 disjoint ranges - one below the new row, and one above, which is usually not what you want to happen.
To make things worse, Excel not only does this not just for the rule with
the relative reference, but for every rule on the worksheet, even the ones
that don't need the 'fix'.
Excel does this 'fix' because the standard behavior with relative references and inserted rows or columns is to 'help' you by
automatically 'fixing' the references so that they continue to refer to the same cells they did before. Unfortunately for
a conditional formatting rule, this doesn't work too well, because now the formula is consistent for every row in the range
but one of them, where the reference is two cells away instead of one.
To make it work in the traditional way, Excel has to 'fix' things by breaking your rule into
two parts - one part with two disjoint ranges that has the formula
unchanged, and one part for a single row that has an adjusted formula.
To prevent Excel from trying
to 'help' you in this way, use the INDIRECT function instead to read the neighboring cells' values in your formula, or
write your own VBA function to get the neigboring cells' values.
- Conditional Formatting Formulas Must Have References - If you have a conditional formatting formula that references
a UDF defined in another workbook such as an add-in, then you must add a
Reference to that workbook before you can reliably use the functions defined
in it. If you don't, you will (sometimes) get the error
'This type of reference cannot be used in a Conditional Formatting Formula'. To
add a reference, press Alt+F11 to go to the code editor, then go to Tools,
References, and add the reference to this workbook by project name for the
project containing the VBA code you need.
- Other Gotchas with UDFs in Conditional Formatting Formulas - If you have a conditional formatting formula that references a UDF, keep the following things in mind. The call to the UDF should be prefixed with the module name. If a crash occurs when running a UDF, you won't receive any indication that the formula failed - instead the conditional formatting
rule will simply not be applied. UDFs can be expensive - they are orders of magnitude slower than built-in functions, and
the will be invoked once per refresh for every used cell in the 'Applies To' range - so use them sparingly. Breakpoints
in a UDF will not be hit when calling a UDF from a conditional formatting expression, but everything else will run
normally. You can increment/update static variables to keep track of the number of times the UDF has been called,
either from a conditional formatting formula, or from any other source.