Multiple Conditional Formatting Rules for a Cell in Excel

Apply multiple Conditional Formatting rules to a cell or range in Excel. This allows you to change the way a cell looks based on different scenarios and to manage which formatting has precedence.

Sections:

Apply Multiple Conditional Formatting Rules

Manage Multiple Conditional Formatting Rules

Notes

Apply Multiple Conditional Formatting Rules

Select the cell or range to format and go to the Home tab > Conditional Formatting and choose a pre-defined rule from the menu or click New Rule at the bottom of the menu and make your own rule.

To apply another conditional format to the cell, make sure the same cell is still selected and go through the above process again.

You can add many conditional formats to the same cell and range in order to get the desired effect.

Manage Multiple Conditional Formatting Rules

Once you have multiple rules, go to the Conditional Formatting Rules Manager.

Home tab > Conditional Formatting > Manage Rules

Here you can see that I have three rules: one to check if the cell has a value in it; one to check if the cell is greater than 1; and one to check if the cell is greater than 3.

Order of Precedence

The above rules are out of order and so the conditional formatting will not work as expected.

If the cell is greater than 1, the background should be red, but if the cell is greater than 3, the background should be blue.

However, since the rule for the red background is on top of the blue background, the cell will never be able to have a blue background because the red one will always take precedence over the blue one.

So, no matter what, once the cell value is greater than 1, the background will be red.

To fix this, we must move the blue rule above the red rule.

Select the blue background rule and then click the up arrow at the top of the window to move it above the red background rule:

Now, when the cell is over 1, the red background will be applied, but when the cell is over 3, the blue background will be applied.

Basically, you have to put the rules in what seems like reverse order to get them to apply correctly when they format the same aspect of the cell, such as background color.

Notes

When each rule applies to a different aspect of formatting, you won't have to worry about the order of precedence so much; the issue arises when multiple rules are adjusting the same formatting element.

Make sure to download the sample file to work with this example in Excel.