OmniDocStop If True in Excel Conditional Formatting Rules
Learn how the Stop If True option and rule priority decide which format wins when several conditional formatting rules match one Excel cell.
If you have ever set two conditional formatting rules on the same Excel range and seen only one of them take effect, you have run into the question of rule priority. The cell matched both rules, yet the color or font you expected did not appear. Searches for conditional formatting excel stop if true usually come from people trying to make one rule override another without breaking the rest of their formatting.
This article explains what the Stop If True option does, how Excel decides which format wins when several rules match a cell, and how to set up rules so they behave the way you intend. The examples use a simple sales sheet, so you can reproduce each one in a few minutes.
Which format wins when several rules match? The short answer
When several rules match a cell, Excel applies them from the top of the rule list down. If two rules set the same format property, the higher rule wins. Stop If True goes one step further: when that rule matches, Excel ignores every rule below it for that cell.
| Your situation | What to do | Why |
|---|---|---|
| One status color should replace all others for a value band | Put that rule at the top and turn on Stop If True | Lower rules are skipped, so they cannot change the cell |
| Two rules should layer, such as a fill and bold text | Leave Stop If True off on both rules | Formats that do not conflict combine |
| Two rules set different fill colors on the same cell | Move the preferred rule higher in the list | The higher rule wins any property both rules set |
| A lower rule never shows | Check rule order and whether a higher rule with Stop If True matches first | The earlier rule may be stopping evaluation |
| The rule type is a data bar or icon set | Open the rule dialog and confirm the option is available | Not every rule type offers Stop If True in the same way |
What Stop If True actually does
Stop If True is a setting on an individual formatting rule. It tells Excel: if this rule is true for a cell, do not check any rules with lower priority for that cell. The rule does not delete the other rules, and it does not change them. It only controls whether they are evaluated for that cell.
The option has no effect when the rule is false. If the condition does not match, Excel moves on to the next rule as usual. This is a common source of confusion: people turn on Stop If True expecting a rule to "lock" the cell, but the lock only happens when the condition is met.
How the order is decided
Excel lists conditional formatting rules in priority order in the rule manager. The rule at the top has the highest priority. You can change the order by moving rules up or down. Priority applies across the whole sheet for rules that overlap a cell, not just within one range, so a rule on a different range can still affect a cell if the ranges overlap.
Consider this example. Column B holds sales amounts, and you set up two rules on the same range:
- Rule 1 (top): Cell value greater than 1000. Green fill. Stop If True on.
- Rule 2: Cell value greater than 500. Yellow fill and bold text. Stop If True off.
With a value of 1200, Rule 1 matches. Excel applies the green fill and then stops, so Rule 2 never runs. The cell is green and not bold. With a value of 700, Rule 1 does not match, so Rule 2 applies. The cell is yellow and bold. With a value of 300, neither rule matches.
Conflicting and non-conflicting formats
Excel handles two kinds of overlap differently. A conflict happens when two matching rules set the same property, such as fill color. The higher rule wins that property. A non-conflict happens when rules set different properties, such as a fill in one rule and bold text in another. Without Stop If True, both formats apply.
Stop If True changes this behavior. If the higher rule has the option turned on and matches, even non-conflicting formats from lower rules are blocked. That is why the 1200 cell above is not bold.
Common misconceptions
Stop If True makes the top rule the only rule. It does not. It only blocks lower rules when the top rule matches. Rules below it still apply to cells where the higher rule is false.
Turning on Stop If True is the same as deleting other rules. Deleting a rule removes it from the sheet. Stop If True leaves the rule in place and only affects one cell at a time, based on whether the condition matches.
A rule that does not show must be broken. Often the rule is working, but a higher rule with Stop If True is matching first. Check the order and the condition of each rule above it.
Order is the same for every rule type. Some rule types, such as data bars, use the option differently or do not offer it. Read the rule dialog before assuming a setting will apply.
📊 At a glance
| Setup | What the reader sees for a matching cell |
|---|---|
| Higher rule matches, Stop If True on | Only the higher rule's formats apply |
| Higher rule matches, Stop If True off | Higher rule wins conflicts; non-conflicting lower formats also apply |
| Higher rule does not match | Lower rules are checked as normal |
| Rules are reversed in order | The former lower rule now wins conflicts and may block others |
Putting it to use
- Select the range that your rules will cover, such as B2:B200 in the sales example.
- Open the rule manager for the current sheet and list every rule that applies to the range.
- Put the rule you want to win conflicts at the top. Check that it has the condition you intend.
- Edit the top rule and turn on Stop If True only if you want it to block all lower rules when it matches. Leave it off if you want formats to layer.
- Test with at least three values: one that matches the top rule, one that matches only a lower rule, and one that matches neither. Use a boundary value, such as exactly 1000, to confirm whether the comparison includes it.
- If a result is wrong, reorder the rules and test again. Change one thing at a time so you can see which change fixed the problem.
❓ FAQ
1. Does Stop If True work with data bars?
It depends on the rule type and your version of Excel. Some rule types do not offer the option in the same way, so open the rule dialog and check what is available before relying on it.
2. Why does my lower rule still show on some cells?
The higher rule is probably false for those cells, so Excel continues down the list. Stop If True only blocks lower rules when the higher rule matches.
3. Can I pause a rule without deleting it?
Yes, you can turn off Stop If True or move the rule lower in the list to change its effect. Keeping the rule in place makes it easier to restore the original behavior later.
4. Do rules on different ranges still interact?
They can, if the ranges overlap. Excel checks rules by priority for each cell, so a rule on a wider range can affect a cell that another rule also covers. Check the "Applies to" range of each rule when a result looks unexpected.
📝 Summary
Stop If True ends rule checking for a cell once a matching rule has been applied, so lower-priority rules do not run for that cell. Rule order decides which format wins conflicts, and the option decides whether non-conflicting formats from lower rules still combine. Set the order first, turn on Stop If True only where you want blocking, and test with values that sit at each boundary.
