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

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.

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:

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.