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.
Use Stop If True when conditional formatting in Excel
Use Stop If True when conditional formatting in Excel
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.
Related articles:
- 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?
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite ā Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license ā set up in minutes (MSI-ready)
- Works better together ā streamlined productivity across Office apps
- 30-day full-featured trial ā no registration, no credit card
- Best value ā save vs buying individual add-in