Manage conditional formatting rule precedence

When you create more than one conditional formatting rule for a range of cells, it is important that you understand how these rules are evaluated, what happens when there are two or more rules in conflict, how copying and pasting can affect rule evaluation, how to change the precedence of evaluation, and when to stop rule evaluation.

What do you want to do?

Learn about conditional formatting rule precedence

Edit conditional formatting rule precedence

Learn about conditional formatting rule precedence

You create, edit, delete, and view all conditional formatting rules in the workbook by using the Conditional Formatting Rules Manager dialog box. (On the Home tab, in the Styles group, click the arrow next to Conditional Formatting, and then click Manage Rules.) When two or more conditional formatting rules apply to a range of cells, these rules are evaluated in order of precedence (top to bottom) by how they are listed in this dialog box.

In this example, cells with employee ID numbers who have certification dates due to expire within 60 days are formatted in yellow, and ID numbers of employees with an expired certification are formatted in red. The rules are shown in the following image.

The first rule (which, if true, sets cell background color to red) tests a date value in column B against the current date (obtained by using the TODAY function in a formula). The formula must be assigned to the first data value in column B, which is B2. The formula for this rule is =B2<TODAY. This formula is used to test the cells in column B (cells B2:B15). If the formula for any cell in column B evaluates to true, its corresponding cell in column A (for example, A5 corresponds to B5, A11 corresponds to B11), is then formatted with a red background color. After all the cells specified under Applies to are evaluated with this first rule, the second rule is tested. This formula checks if values in the B column are less than 60 days from the current date (for example, suppose today's date is 8/11/2010). The cell in B4, 10/4/2010, is less than 60 days from today, so it evaluates as true, and is formatted with a yellow background color. The formula for this rule is =B2<TODAY()+60. Any cell that was first formatted red by the highest rule in the list is left alone.

A rule higher in the list has greater precedence than a rule lower in the list. By default, new rules are always added to the top of the list and therefore have a higher precedence, but you can change the order of precedence by using the Move Up and Move Down arrows in the dialog box.

What happens when more than one conditional formatting rule evaluates to true

For a range of cells, you can have more than one conditional formatting rule that evaluates to true. Either the rules don't conflict or they conflict:

When rules don't conflict     For example, if one rule formats a cell with a bold font and another rule formats the same cell with a red color, the cell is formatted with both a bold font and a red color. Because there is no conflict between the two formats, both rules are applied.

When rules conflict     For example, one rule sets a cell font color to red and another rule sets a cell font color to green. Because the two rules are in conflict, only one can apply. The rule that is applied is the one that is higher in precedence (higher in the list in the dialog box).

How pasting, filling, and the Format Painter affect conditional formatting rules

While editing your worksheet, you may copy and paste cell values that have conditional formats, fill a range of cells with conditional formats, or use the Format Painter. These operations can affect conditional formatting rule precedence in the following way: a new conditional formatting rule based on the source cells is created for the destination cells.

If you copy and paste cell values that have conditional formats to a worksheet opened in another instance of Excel (another Excel.exe process running at the same time on the computer), no conditional formatting rule is created in the other instance and the format is not copied to that instance.

What happens when a conditional format and a manual format conflict

For a range of cells, if a formatting rule is true, it takes precedence over a manual format. You apply a manual format by using the Format command in the Cells group on the Home tab. If you delete the conditional formatting rule, the manual formatting for the range of cells remains.

Note: Manual formatting is not listed in the Conditional Formatting Rules Manager dialog box nor is it used to determine precedence.

Controlling when rule evaluation stops by using the Stop If True check box

For backwards compatibility, you can select the Stop If True check box in the Manage Rules dialog box to simulate how conditional formatting might appear in earlier versions of Excel that do not support more than three conditional formatting rules or multiple rules applied to the same range.

For example, if you have more than three conditional formatting rules for a range of cells, and are working with a version of Excel earlier than Excel 2007, that version of Excel:

• Evaluates only the first three rules.

• Applies the first rule in precedence that is true.

• Ignores rules lower in precedence if they are true.

The following table summarizes each possible condition for the first three rules:

If rule	Is	And if rule	Is	And if rule	Is	Then
One	True	Two	True or False	Three	True or False	Rule one is applied and rules two and three are ignored.
One	False	Two	True	Three	True or False	Rule two is applied and rule three is ignored.
One	False	Two	False	Three	True	Rule three is applied.
One	False	Two	False	Three	False	No rules are applied.

You can select or clear the Stop If True check box to change the default behavior:

• To evaluate only the first rule, select the Stop If True check box for the first rule.

• To evaluate only the first and second rules, select the Stop If True check box for the second rule.

• To evaluate only the first, second, and third rules, select the Stop If True check box for the third rule.

Note: You cannot select or clear the Stop If True check box if the rule formats by using a data bar, color scale, or icon set.

Top of Page

Edit conditional formatting rule precedence

1. On the Home tab, in the Styles group, click the arrow next to Conditional Formatting, and then click Manage Rules.

   

   The list of conditional formatting rules are displayed for the current selection including the rule type, the format, the range of cells the rule applies to, and the Stop If True setting.

   If you don't see the rule that you want, in the Show formatting rules for list box, make sure that the appropriate range of cells, worksheet, table, or PivotTable report is selected.

2. Select a rule. Only one rule can be selected at a time.

3. To move the selected rule up in precedence, click Move Up. To move the selected rule down in precedence, click Move Down.
   
   
   

4. Optionally, to stop rule evaluation at a specific rule, select the Stop If True check box.

Top of Page