Multiple Rules Hierarchy

Using multiple rules in Excel conditional formatting is not rocket science. Learn how the rules hierarchy and overlapping are works. If you are not familiar with the hierarchy and rules overlapping, this article is yours!

What is this mean? The rules are subordinate to each other. Is more rule 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 have to keep in mind the rules’ hierarchy.

This tutorial is a part of our definitive guide on ‘How to use conditional formatting in Excel?‘

Using Multiple Rules in Excel

1. The first and most important thing is that the newer rules will always assume precedence over the older ones. So try to think by the reversed logic sequence order.
2. When Excel analyses and apply the rules relevant to a cell, then there are two options. In the first one, the rules do not conflict. The cells return with the awaited highlights (like yellow mark by red background color). In the second one, the rules conflict, and we will lose the highlights of conditional formatting has created. And on top of all that, we wouldn’t know which one. If we build our rules wisely, they will not conflict.
3. Rules don’t conflict when they apply to different properties of the cell. We can use endless formatting methods, so we only talk about one single example here.

Example: Multiple Rules Hierarchy – Overlapping (Solution)

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.

Because we alter the cell’s two different properties when we run the rules, there will be no negative effect. 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 the analysis of both rules, we will experience the following. There will be cells with a yellow background and with red font color. So, we can recognize that here two rules have crossed each other’s way!

Explanation: Because we use two different properties of the cell, we can easily see the mistake. We call them mixed cells. If both of these rules had operated with the background color, we would be in big trouble!

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 is meeting the conditions, but rule 1 will be applied regarding the multiple rules hierarchy.

How to modify the order of rules?

1. Change the first field from ‘Current Selection’ to ‘This Worksheet’ in the drop-down menu.
2. Select the first rule. The rule will be highlighted.
3. Click the move-down arrow!
4. Click OK

Now it looks different. The rule that makes all cells in red if they are below 300 is at the top of the Conditional Formatting Rules Manager window.

Stay tuned, download the practice file.