Conditional Formatting Rules in the Right Order
Two rules color the same cells, and the wrong one wins. Rules apply top to bottom, so the order of the list decides which format you actually see.
TL;DR / Quick Fix
Quick fix: open the conditional formatting rules manager and read the list top to bottom. Then drag the rule that should win to the top, check the stop if true boxes, and look at the sheet to confirm the right colors appear.
Follow these steps
- Open the rules manager. Find conditional formatting rules in the format menu. The manager shows every rule in the order they apply, top to bottom.
- Read the list top to bottom. The first rule that matches a cell usually wins. A rule sitting below a matching rule above it may never show.
- Drag the winning rule to the top. Reorder so the most important rule comes first. Order is the main control you have over conflicting formats.
- Use stop if true where offered. Check stop if true on a rule to block lower rules from also formatting the same cells. That checkbox ends most color fights.
- Test with one cell. Change one cell's value through the rule thresholds and watch its color. One live cell proves the order works better than staring at the list.
- Delete duplicate or dead rules. Remove rules that overlap or no longer matter. Fewer rules mean fewer order surprises.
What NOT to do
- Do not add a third rule to fix a conflict. Reorder the two you have, since more rules only deepen the ordering puzzle.
- Do not ignore the rule order. Top to bottom is the whole game, and a perfect rule in the wrong position never shows.
- Do not leave stop if true unchecked on exclusive rules. Without it, lower rules can paint over the winner.
- Do not format by hand over conditional formats. Manual color fights the rules, and the next recalculation can wipe your hand coloring.
Frequently asked questions
Why does the wrong rule color my cells?
Because rules apply top to bottom, and a higher rule that matches wins over the one you expected.
What does stop if true do?
It stops lower rules from also formatting cells the rule already colored, ending most conflicts.
Should I just delete and restart?
Only as a last resort. Reordering and stop if true fix most conflicts without rebuilding.