Multiple Conditional Formatting Rules for a Cell in Excel

Add to Favorites

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.

b0f7b3f2f12633986979481d59df1535.jpg

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

19986d34005e8b4f7e93674f74114f2b.png

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:

c194127cdc0d357ebfc5ff02d4b2e39d.jpg

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.


Downloadable Files: Excel File

Similar Content on TeachExcel
Format Cells as a Percentage in Excel Number Formatting
Macro: This free Excel macro formats a selection of cells as a Percentage in Excel. This simply c...
Format Cells as a Fraction in Excel Number Formatting
Macro: This free Excel macro will automatically format a selected cell or many selected cells in ...
Format Cells as a Currency in Excel Number Formatting
Macro: This free Excel macro allows you to quickly and easily format a selection of cells in the ...
Remove All Data Validation from a Cell in Excel
Macro: Remove all data validation from a cell in Excel with this free Excel macro. This is a grea...
Extract the First Word from a Cell in Excel - User Defined Delimiter Text Extraction - UDF
Macro: This free Excel UDF (user defined function) returns the first word from a cell in Exce...
Reverse the Contents of a Cell in Excel - UDF
Macro: Reverse cell contents with this free Excel UDF (user defined function). This will mir...