Manage conditional formatting rule precedence Applies To: Less When you create more than one conditional formatting rule for a range of cells, it helps to understand in what order these rules are evaluated, what happens when two or more rules conflict, how copying and pasting can affect rule evaluation, how to change the order in which rules are evaluated, and when to stop rule evaluation.
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. 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. Here's an example that has expiration dates for ID badges.
We want to mark badges that expire within 60 days but are not yet expired with a yellow background color, and expired badges with a red background color. 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. Assign the formula to the first data value in column B, which is B2. This formula tests the cells in column B cells B2: 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. 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, so you'll want to keep an eye on their order. 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. Here's how rules are applied, first when rules don't conflict, and then when they do conflict: Because there is no conflict between the two formats, both rules are applied.
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: If you copy and paste cell values that have conditional formats to a worksheet opened in another instance of Excel another Excel.
What happens when a conditional format and a manual format conflict For a range of cells, if a formatting rule is evaluated as True, it takes precedence over an existing manual format. If you delete the conditional formatting rule, the manual formatting for the range of cells remains.
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 with versions of Excel earlier than Excel , you can select the Stop If True check box in the Manage Rules dialog box to simulate how conditional formatting might appear in those 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 , 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: