Skip to main content

How to use Stop If True when conditional formatting in Excel?

Author: Siluvia Last Modified: 2020-07-01

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:

Best Office Productivity Tools

🤖 Kutools AI Aide: Revolutionize data analysis based on: Intelligent Execution   |  Generate Code  |  Create Custom Formulas  |  Analyze Data and Generate Charts  |  Invoke Kutools Functions
Popular Features: Find, Highlight or Identify Duplicates   |  Delete Blank Rows   |  Combine Columns or Cells without Losing Data   |   Round without Formula ...
Super Lookup: Multiple Criteria VLookup    Multiple Value VLookup  |   VLookup Across Multiple Sheets   |   Fuzzy Lookup ....
Advanced Drop-down List: Quickly Create Drop Down List   |  Dependent Drop Down List   |  Multi-select Drop Down List ....
Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Toggle Visibility Status of Hidden Columns  |  Compare Ranges & Columns ...
Featured Features: Grid Focus   |  Design View   |   Big Formula Bar    Workbook & Sheet Manager   |  Resource Library (Auto Text)   |  Date Picker   |  Combine Worksheets   |  Encrypt/Decrypt Cells    Send Emails by List   |  Super Filter   |   Special Filter (filter bold/italic/strikethrough...) ...
Top 15 Toolsets12 Text Tools (Add Text, Remove Characters, ...)   |   50+ Chart Types (Gantt Chart, ...)   |   40+ Practical Formulas (Calculate age based on birthday, ...)   |   19 Insertion Tools (Insert QR Code, Insert Picture from Path, ...)   |   12 Conversion Tools (Numbers to Words, Currency Conversion, ...)   |   7 Merge & Split Tools (Advanced Combine Rows, Split Cells, ...)   |   ... and more

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...

Description


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!
Comments (6)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
So I need a formula that stops the conditional formatting rule in the O column if the P column gets info added.
This comment was minimized by the moderator on the site
I write Stop is True in cell as article say do but run the macro and go kaput. You sure? Also. Why can not rely on SUM formula to give correct value need to be next article. I enter A1, B6 and it give me 33, but answer should result in C7. Then we need more tutorial for advantced excel user with how vlookup work when try to find answer to question. I better than most players at excel but I need some little help getting answer in this case. Most people just gotta get they're hand held to get big picture but I know better than them.
This comment was minimized by the moderator on the site
Say something here.
This comment was minimized by the moderator on the site
Sumatr ethiec omato´ning
This comment was minimized by the moderator on the site
Don'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?
This comment was minimized by the moderator on the site
How do we prevent a user from skipping a checkbox in a form?
There are no comments posted here yet
Leave your comments
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations