Using multiple rules in Excel conditional formatting is not rocket science. Learn how the rules hierarchy and overlapping work.
If you are unfamiliar with the hierarchy and rules overlapping, this article is yours! What does this mean? The rules are subordinate to each other. Are more rules applying to one cell than Excel has to decide by precedence rules that will prioritize? What will happen when one rule overwrites the other? In this case, we may lose the given cell’s format. To prevent these unpleasant and unexpected events, we must keep the rules’ hierarchy in mind.
This tutorial is a part of our definitive guide on ‘How to use conditional formatting in Excel?‘
Using Multiple Rules in Excel
- The first and most important thing is that the newer rules will always assume precedence over the older ones. So, try to think using the reverse logic sequence order.
- When Excel analyses and applies the rules relevant to a cell, there are two options. The rules do not conflict in the first one, and the cells return with the awaited highlights (like a yellow mark on a red background). In the second one, the rules conflict, and we will lose the highlights of conditional formatting created. And on top of all that, we wouldn’t know which one. If we build our rules wisely, they will not conflict.
- Rules don’t conflict when they apply to different properties of the cell. We can use endless formatting methods, so we only discuss one example here.
Overlapping Conditional Formatting Rules
Let’s assume that there are multiple rules regarding one cell.
- If rule 1 is TRUE, the font is color red.
- If rule 2 is TRUE, the cell color is yellow.
There will be no negative effect because we alter the cell’s two different properties when we run the rules. Let’s see the possible causes. Overlap or the lack of it will change the highlight like this:
Where Excel gives a TRUE value for analyzing both rules, we will experience the following. There will be cells with a yellow background and a red font color. So, we can recognize that here, two rules have crossed each other’s way!
Explanation: We can easily see the mistake using two different cell properties. We call them mixed cells. We would be in big trouble if both of these rules had operated with the background color!
To adjust the multiple rules hierarchy in Excel, follow these steps. Just a few words about these two rules:
Rule 1: =$M2<$N$1-150
Explanation: All entries updated more than 150 days before the date in cell N1 must have their entire row colored yellow.
Rule 2: If quantities are less than 300, we must use a red fill.
Now apply the current conditional formatting rules to check the Worksheet. Important! Check cell H8! Rule 1 overwrite rule 2! Cell H8 and cell M8 meet the conditions, but rule 1 will be applied regarding the multiple rules hierarchy.
How do we modify the order of rules?
- Change the first field from ‘Current Selection’ to ‘This Worksheet’ in the drop-down menu.
- Select the first rule. The rule will be highlighted.
- Click the move-down arrow!
- Click OK
Now, it looks different. The rule that makes all cells red if they are below 300 is at the top of the Conditional Formatting Rules Manager window.
Stay tuned, and download the practice file.