Amal Nagm

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

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 by how they are listed in this dialog box.

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.

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 Microsoft Office Excel, a conditional formatting rule is not created and the format is not copied.

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 or 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 Microsoft Office 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, a version of Excel earlier than Office Excel 2007:

• Only evaluates 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:

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.

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.