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.
- Reuse Anything: Add the most used or complex formulas, charts and anything else to your favorites, and quickly reuse them in the future.
- More than 20 text features: Extract Number from Text String; Extract or Remove Part of Texts; Convert Numbers and Currencies to English Words.
- Merge Tools: Multiple Workbooks and Sheets into One; Merge Multiple Cells/Rows/Columns Without Losing Data; Merge Duplicate Rows and Sum.
- Split Tools: Split Data into Multiple Sheets Based on Value; One Workbook to Multiple Excel, PDF or CSV Files; One Column to Multiple Columns.
- Paste Skipping Hidden/Filtered Rows; Count And Sum by Background Color; Send Personalized Emails to Multiple Recipients in Bulk.
- Super Filter: Create advanced filter schemes and apply to any sheets; Sort by week, day, frequency and more; Filter by bold, formulas, comment...
- More than 300 powerful features; Works with Office 2007-2019 and 365; Supports all languages; Easy deploying in your enterprise or organization.
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.· 11 months agoSumatr ethiec omato´ning
To post as a guest, your comment is unpublished.· 1 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.· 2 years agoHow do we prevent a user from skipping a checkbox in a form?