The Stop if True function can stop processing the current rule when the condition meets and ignore the other rules. Let us see how it works!
It often happens that with the use of conditional formatting, we create multiple rules.
Use Stop If True When Conditional Formatting In Excel
Let’s see a simple example: In a given range that contains prices, we would like to apply two rules simultaneously.
Rule 1: A traffic light icon set (red-yellow-green) will indicate the value in the given cell compared to the other cell values. Red is the upper 1/3rd, yellow is the middle 1/3rd, and green is the bottom third.
Rule 2: Emphasize the prices that belong in the upper 1/3rd, but here use a different formatting method! This is not a bad idea on its own!
Will there be an overlap between the rules?
Yes, there will be!
Because based on the applied icon set rule, it will fill in all the cells. And this will contain the values that belong to the upper 1/3rd. So how can we deal with the problem?
Excel conditional function offers an elegant solution. Let’s see what happens when we use the Stop if True tool.
We selected the J2:J24 range and used a traffic light shape set to the J column to highlight low, middle, and high-priced products.
But what will happen if we want to highlight the top 33% of the purchase price too? If we create a new rule, we provide extra information, and it’s not necessary. To avoid visual accidents, we’ll use the stop if true rule.
Click OK to return to the Manage Rules box.
Check the stop if true box to the right of the new rule you just created.
Click OK.
Looks great! The given range will only show the icon sets for the items, NOT in the top 33%.
