Use conditional formatting to highlight information in Excel
How to use conditional formatting in Excel to visually explore, analyze, and identify patterns and trends.
/apple-touch-icon.png
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, click Conditional Formatting, and then click Manage Rules.)

The Conditional Formatting Rules Manager dialog box appears.

When two or more conditional formatting rules apply, 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. The formula for this rule is =B2<TODAY(). This formula tests 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, 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

Sometimes you 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:

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).

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

For backwards compatibility, 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 an earlier version of 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:

If rule

Is

And if rule

Is

And if rule

Is

Then

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.

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