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 blog 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 static values).

    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.