How to use Stop If True when conditional formatting in Excel?
In Microsoft Excel, you can apply multiple conditional formatting rules together. But if you want to stop processing the other rules when the first condition is achieved, what can you do? Actually, the conditional formatting function provides you a Stop if True utility which can stop processing the current rule when the condition meets and ignore the other rules. This tutorial will show you how to use the Stop If True utility in conditional formatting.
Please do as follows to know how to use the Stop If True in conditional formatting in Excel.
Take the icon sets as example, please look at the below screenshot:
The green icons signify the values which are >=90, and the yellow icons signify the values which are < 90 and >= 80, finally the red icons signify the values which are < 80.
Now I just want to keep the green icons in the range to only inform me about the value which are >=90.
1. Select the cell range and click Conditional Formatting > Manage Rules under Home tab. See screenshot:
2. In the Conditional Formatting Rules Manager dialog box, click the New Rule button.
3. In the Edit Formatting Rule dialog box, you need to:
1). Select Format only cells that contain in the Select a Rule Type box;
2). In the Edit the Rule Description section, specify your rule conditions. In this case, I just want to keep the green icons which values are >=90, so under the Format only cells with section, please choose Cell Value then select not between, and in the last two text boxes, enter the criteria you need, this example, I will enter =90 and =100. ;
3). Here I choose no format;
4). Click the OK button. See screenshot:
4. Then it returns to the Conditional Formatting Rules Manager dialog box, please remember to check the Stop If True box for the rule you created above. Then click the OK button.
Then you can see all the yellow and red icons are gone and only keep the green ones.
- How to conditional format cells if containing #na in Excel?
- How to conditional format dates less than/greater than today in Excel?
- How to conditional format or highlight first recurrence in Excel?
- How to conditional format negative percentage in red in Excel?
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
- To post as a guest, your comment is unpublished.· 5 days agoSay something here.
- To post as a guest, your comment is unpublished.· 1 years agoSumatr ethiec omato´ning
- To post as a guest, your comment is unpublished.· 2 years agoDon't get it. So what is the point of doing the first Conditional Formatting? Wouldn't it be easier to do only green dots at the first place?
- To post as a guest, your comment is unpublished.· 3 years agoHow do we prevent a user from skipping a checkbox in a form?