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.
Recommended Productivity Tools for Excel
Office Tab: Bring powerful tabs to Office (include Excel), just like Chrome, Safari, Firefox and Internet Explorer. Save you half the time, and reduce thousands of mouse clicks for you. 30-day Unlimited Free Trial
Kutools for Excel: Save 71% of your time and solve 82% Excel problems for you. 300+ advanced tools designed for 1500+ work scenario, make Excel much easy and increase productivity immediately.60-day Unlimited Free Trial
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?
Recommended Productivity Tools
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 months agoSumatr ethiec omato´ning
To post as a guest, your comment is unpublished.· 6 months 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.· 2 years agoHow do we prevent a user from skipping a checkbox in a form?