Part of the free Module 7: Conditional Formatting · Lesson 15 of 15 · Full Excel course
Updated on 5 September 2026 · Works in Excel 365, 2021, 2019 and 2016 unless noted.
Conditional formatting rule precedence is the order in which Excel evaluates rules that apply to the same cell. In the Conditional Formatting Rules Manager, the rule at the top of the list has the highest precedence: when two rules are both true and try to set the same format, such as a fill colour, the higher rule wins. Stop If True goes further and stops Excel from evaluating the rules below it for any cell where that rule is true.
What happens when two rules are true at once
Every cell can carry several rules. Excel evaluates them from the top of the Rules Manager downwards and combines the results with two principles:
- Different properties stack. If one rule sets a red fill and another sets bold text, a cell that satisfies both gets a red fill and bold text. There is no conflict, so both apply.
- The same property is won by the higher rule. If one rule sets a red fill and another sets a green fill, a cell that satisfies both gets the fill from whichever rule sits higher in the list. The lower rule’s fill is ignored for that cell, but its other properties still apply.
Data bars, colour scales and icon sets follow the same logic. They are treated as a format type, so a cell cannot show an icon set from two rules, but it can show an icon set from one rule and a font colour from another. Manual formatting applied with the Home tab never wins against conditional formatting: a conditional fill always covers a manual fill while the condition is true.
Step by step: reorder rules in the Rules Manager
- Select any cell in the range, then go to Home > Conditional Formatting > Manage Rules.
- In Show formatting rules for, choose This Worksheet to see every rule on the sheet, or leave Current Selection to see only the rules on the selected cells.
- Click the rule you want to promote and click the Move Up arrow, or Move Down to demote it. The topmost rule has the highest precedence.
- Tick Stop If True on a rule if the rules below it should be skipped for the cells it matches.
- Click Apply to see the result without closing the dialog, then OK.
New rules are always added at the top of the list, which is the opposite of what most people expect. If you build rules in the order “green for good, amber for warning, red for critical”, the red rule ends up on top. That often works by accident, because the most urgent condition should win, but check the order every time you add a rule.
How Stop If True changes the result
When a rule with Stop If True is TRUE for a cell, Excel applies that rule’s format and evaluates no further rules for that cell. When the rule is FALSE, evaluation continues down the list as normal. It affects only the cells where the rule is true, not the whole range.
| Situation | Without Stop If True | With Stop If True on rule 1 |
|---|---|---|
| Rule 1 red fill, rule 2 bold font, both TRUE | Red fill and bold | Red fill only |
| Rule 1 red fill, rule 2 green fill, both TRUE | Red fill (rule 1 wins the fill) | Red fill (same result, rule 2 never runs) |
| Rule 1 highlight “Done”, rule 2 icon set, both TRUE | Highlight plus icon | Highlight, no icon |
| Rule 1 FALSE, rule 2 TRUE | Rule 2 applies | Rule 2 applies (stop only fires on TRUE) |
The classic use is to suppress data bars or icon sets on certain cells. A rule such as =$C2="N/A" with no format at all and Stop If True ticked sits above the icon set rule; cells marked N/A get nothing, every other cell gets its icon. The same trick hides a colour scale on subtotal rows or blanks. Stop If True also matters for compatibility: Excel 2003 could show only one conditional format per cell, so files saved in the old .xls format apply Stop If True to every rule automatically.
Rules Manager options that affect precedence
| Control | What it does |
|---|---|
| Move Up / Move Down | Changes precedence; the top rule wins conflicts. |
| Stop If True | Skips lower rules for cells where this rule is TRUE. |
| Applies to | Rules only compete on cells they share; a rule on A2:A20 never conflicts with one on B2:B20. |
| Show formatting rules for | Filters the list. Rules on the same cells still compete even when hidden by the filter. |
| Duplicate Rule | Excel 365 and 2019 onwards: copies a rule so you can vary the condition without rebuilding the format. |
| Delete Rule | Removes the rule from every cell in its Applies to range. |
Worked example: a three-band status column
A project tracker has a percentage complete in column C and a status in column D. The manager wants: green fill when complete is 100 percent, amber fill when it is between 50 and 99 percent, red fill below 50 percent, and every row marked Cancelled shown in grey with no other colour. Sample data in A2:D6:
| Task | Owner | Complete | Status |
|---|---|---|---|
| Design | Priya | 100% | Active |
| Build | Amit | 65% | Active |
| Test | Neha | 20% | Active |
| Launch | Rahul | 0% | Cancelled |
Apply four formula rules to A2:D6 and arrange them in this order in the Rules Manager:
=$D2="Cancelled", grey fill, Stop If True ticked.=$C2=1, green fill.=$C2>=0.5, amber fill.=$C2<0.5, red fill.
Each formula uses $C2 or $D2: the column is locked so every cell in the row tests the same column, and the row is relative so each row tests itself. The formula is written for A2, the top-left cell of the range, and Excel shifts the row number for every other row.
Result: Design is green, because rule 2 is true and no higher rule sets a fill. Build is amber; 65 percent satisfies rule 3 only. Test is red. Launch is grey and nothing else, because rule 1 is true for that row and Stop If True prevents rule 4 (0 percent is below 50) from painting it red. Now untick Stop If True on rule 1 and Launch stays grey, because rule 1 is higher and wins the fill anyway. Stop If True makes a visible difference only when the lower rule sets a property the upper rule does not: swap rule 4’s red fill for a red bold font and, without Stop If True, Launch becomes grey with red bold text.
Tips and common mistakes
- New rules go to the top. After adding a rule, open Manage Rules and check it landed where you want.
- Order overlapping numeric bands from strict to loose. Put “equals 100 percent” above “50 percent or more” or the green rule never shows.
- Use Stop If True only when a lower rule sets something the upper rule does not. For plain fill-versus-fill conflicts, order alone decides.
- A blank format plus Stop If True is a mask. It is the standard way to keep icons or bars off headers, totals and N/A cells.
- Check Applies to before blaming precedence. Two rules on different ranges never conflict; the “missing” colour is often a range that stops one row short.
- Do not rely on manual fills. A manual fill is always hidden behind a conditional fill while the rule is true.
- Precedence is per cell, not per sheet. Rule 1 stopping evaluation on one cell does nothing to the neighbouring cell where rule 1 is false.
Errors and how to fix them
| Symptom | Cause | Fix |
|---|---|---|
| Green rule never appears | A looser rule above it also sets a fill | Move the green rule above, or narrow the looser rule’s condition |
| Icons show on cells that should be blank | No masking rule, or the mask sits below the icon rule | Add a no-format rule with Stop If True at the top |
| Both colours seem to apply | The two rules set different properties (fill and font) | Expected; tick Stop If True on the upper rule if the lower one must not apply |
| Stop If True does nothing | The lower rules set the same property the upper rule already wins | No change needed; the checkbox only matters for non-conflicting properties |
| Rule order keeps changing | Rules created from presets are inserted at the top each time | Build all rules, then arrange them once in Manage Rules |
| Rule appears greyed or missing in the list | Show formatting rules for is set to Current Selection | Switch to This Worksheet |
Practice exercise
Open the course practice file or build a small project list with a percentage column, then:
- Create green, amber and red fill rules for 100 percent, 50 percent or more, and below 50 percent. Deliberately add them in that order, open Manage Rules and note which one is on top.
- Reorder the rules so each band shows correctly and record why the amber rule must sit below the green one.
- Add an icon set to the percentage column, then add a no-format rule with Stop If True that hides icons on rows whose status is Cancelled.
- Untick Stop If True and describe what changes; then tick it again.
- Give one rule a bold font instead of a fill and confirm it stacks with the fill from another rule.
Key takeaways
- Rules are evaluated from the top of the Rules Manager down; the highest rule wins any conflict over the same property.
- Rules that set different properties, such as fill and font, both apply to the same cell.
- Stop If True stops evaluation of lower rules only for cells where that rule is true.
- A rule with no format and Stop If True is the standard way to mask icons, bars and scales on chosen cells.
- New rules are inserted at the top, so review the order after every addition.
- Rules only compete on cells they share; check Applies to before adjusting precedence.
Related lessons
- Conditional Formatting course hub
- Manage and Clear Rules
- Icon Sets: traffic lights, thresholds and custom icons
- Conditional formatting based on another cell, a drop-down or a checkbox
- Copy, paste and manage conditional formatting across sheets and Tables
- AND function for combining conditions in one rule
- Microsoft Support: Use conditional formatting to highlight information (rule precedence section)
Frequently asked questions
Which conditional formatting rule takes priority in Excel?
The rule nearest the top of the Conditional Formatting Rules Manager has priority. When two rules are true for the same cell and both set the same property, such as fill colour, the higher rule’s format is used. Properties that do not clash, such as a fill from one rule and a font colour from another, are both applied.
What does Stop If True do in conditional formatting?
When a rule with Stop If True ticked is true for a cell, Excel applies that rule’s format and skips all lower rules for that cell. It has no effect on cells where the rule is false. It is mainly used to stop icon sets, data bars or colour scales appearing on cells that an earlier rule has already handled.
Why does my new conditional formatting rule not work?
Often it does work but is hidden: a rule above it in the Rules Manager sets the same property and wins. Open Manage Rules, choose This Worksheet, and move the new rule up. Also confirm the Applies to range covers the cells you expect, since rules on different ranges never interact.
Do conditional formatting rules override manual cell formatting?
Yes. While a rule’s condition is true, its fill, font colour, border or number format sits on top of any formatting applied by hand. When the condition becomes false, the manual formatting shows again. To remove the effect permanently, clear the rule rather than repainting the cell.
Can I change the order of conditional formatting rules?
Yes. In Home > Conditional Formatting > Manage Rules, select a rule and use the Move Up and Move Down arrows. Click Apply to preview the effect. Because Excel adds every new rule at the top of the list, reordering after building all the rules is normal practice.
Want the finished version? Ready-made Excel KPI dashboards with layered traffic-light rules already ordered correctly are available at NextGenTemplates.com.