Rule Precedence in Conditional Formatting

Conditional Formatting in Excel makes the things easier and user friendly, but at times it may also make it quite confusing. When you are using more than two conditions on similar data then it is known as Conditional Formatting using Multiple Conditions.

In the last blog we showed you How to use Conditional Formatting in easy steps. Today, we shall learn about the Rule Precedence in Conditional Formatting.

What is Rule Precedence?

When two or more Rules are applied to the same range of data then these rules are evaluated on the basis of precedence (Top to Bottom) mentioned in the dialog box of manage rules.

Let us learn more about it by following simple steps:

1. In the image below you will see that the Column C has two formatting. The blue cells highlight the data between 4000 to 8000 whereas the green ones represent data between 8000 to 9000. If you look closely, then you will realize that in the month of September the sales is 8000 which falls in both our rules criteria, but then too it is highlighted as green. Now you are wondering why is that? Well you will get your answer by going through the steps below.

2. While applying the formatting, excel checks the precedence order and then it applies the rules accordingly. To check the precedence order, go to conditional formatting and select the option of Manage Rules.

3. You shall see a new dialog box in which you can see the order of your formatting. In our case the Rule ‘˜cell value between 8000 to 9000’™ is placed above the Rule ‘˜cell value between 4000 to 8000’™. Now this is the answer to your question in step 1. In our data 8000 appears in green because the precedence order of ‘˜cell value between 8000 to 9000’™ is higher than ‘˜cell value between 4000 to 8000.’™

Note: By default, new rules are always added to the top of the list and therefore have a higher precedence, if you want it otherwise, you will have to change the order.

4. If you want to change your precedence order then you can simply select the 1st rule and then click on the downward arrow. You will see that the places of the rules have been shifted. Click on apply.

5. You will see that the data has been re- arranged as per the new precedence order. In our case you will find that 8000 in the month of September is now highlighted in blue instead of green.

If you think that this article has helped you in some way or the other, then don’™t forget to share it with your friends and if you have any suggestions or queries then you can let us know in the comments box below.