When you create more than one conditional formatting rule for a range of cells, it's important that you understand:

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.

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.

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.

    outlook ribbon image

    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.

    Select the Stop If True check box for backwards compatibility with previous versions of Microsoft Office Excel that do not support multiple conditional formatting rules. For example, if you have three conditional formatting rules for a range of cells, by default, a version of Excel previous to Office Excel applies the last rule in precedence. If you want the first or second rule applied, select the Stop If True check box for that rule.

See also: