In Conditional Formatting, you can see a “Stop IF True” checkmark box when you open the manage rules dialog box. And in this tutorial, we will learn to use this checkbox.
What is the use of Stop IF True in Conditional Formatting?
Let’s say you have two rules applied to the same range of cells. With Stop IF True, you can tell Excel not to use the second rule in the cells where the first rule is TRUE. In this tutorial, we take a simple example to understand this.
Example of using Stop If True in Conditional Formatting
In the below example, we have an icon set on a range we have numbers.
In this icon set rule, you have green dots for values above 85, yellow dots for values between 84 to 45, and red dots for values below 45.
Here we need only green dots, and “Stop IF True” checkmark can help us. For this, let’s create a new rule. Go to Home > Conditional Formatting > New Rule.
Now in the new rule dialog box, click on the “Format only cells that contain”, and select “not between”. After that, enter 85 and 100 to create a range of numbers.
With this, you create a rule to format numbers not in this range. Remember, this is the same range of numbers that don’t have a green icon in the already-established icon set rule.
In this rule, you don’t need to specify any formatting. So, click OK to create the rule. And when you click OK, this will do nothing.
Again, go to Home > Conditional Formatting > Rules Manager. And from here, tick marks the “Stop IF True” check box and click Apply.
And the moment you click OK, it will show you a green icon light on the cells where values are above or equal to 85.
Stop IF True works based on the order of rules you have in the rules manager. So when there are cells where the rule is true, it will not apply any other rule to those cells.
And the same thing happens here when the first rule, which we have applied to the cells with the value 0 to 84, is true; it doesn’t apply the second rule to those cells, which is why we only have green icon lights.
Get the Excel File
DownloadMore on Conditional Formatting
- Apply Conditional Formatting to a Pivot Table
- Remove Conditional Formatting
- Apply Conditional Formatting on Blank Cells in Excel
- Compare Two Columns using Conditional Formatting
- Apply Conditional Formatting Based on Another Cell
- Apply Conditional Formatting Based on Another Column
- Copy Conditional Formatting
- Apply Conditional Formatting to an Entire Column in Excel
- Highlight Rows using Conditional Formatting in Excel
- Apply Multiple Conditions in Conditional Formatting in Excel