Conditional formatting is one of those features that seems simple until it isn't. A Reddit user recently posted a frustration many of us have felt: they set rules to color numbers above 10% or below -10% red, and numbers between -5% and +5% green. They left the 5% to 10% range uncolored, no rule at all. Yet those middle values were still showing up with color. The problem isn't a bug. It's a logic gap, and it's worth understanding because it reveals how traditional spreadsheets handle rules behind the scenes.
The user's rules, as described, leave a gap between 5% and 10%. But spreadsheets apply conditional formatting in order of priority, and they often apply a default or overlapping rule if one rule's condition isn't strictly exclusive. In many spreadsheet applications, if a value falls into a range that isn't explicitly covered, the formatting engine may still interpret the nearest rule as applying. The issue is that the "between -5% and +5%" rule is written as inclusive or near-inclusive, and the "above 10%" rule may be treated as strictly greater than 10%, leaving values like 6% or 7% in a no-man's-land. The spreadsheet then either applies no formatting, which can appear as a leftover color from a previous rule, or it applies the green rule because the value is technically "between -5% and +5%" if the boundary is inclusive of 5%. The screenshots show the 5% to 10% range picking up green, which suggests the green rule's upper bound includes 5% rather than stopping at 4.99%.
What this means for you is practical: conditional formatting is not as intuitive as it looks. The spreadsheet doesn't "know" you meant the 5% to 10% range to be uncolored. It only knows the boundaries you typed. If you write "between -5% and +5%," most tools treat that as including 5% exactly. So a value of 5.1% might still trigger the green rule if the rounding or precision works against you. The fix is to be explicit about boundaries, use "greater than or equal to" and "less than" with precise decimals, or layer rules in the correct order so that the red rule for above 10% runs first and stops further formatting. The user's mistake is common, and it's not their fault: the tool assumes you meant inclusive unless you tell it otherwise.
We think this is a perfect example of why traditional spreadsheets can feel like they're working against you. The logic is there, but it's hidden behind a UI that prioritizes simplicity over clarity. An AI-native spreadsheet would handle this differently: it would ask you to confirm boundary behavior, or it would surface the ambiguity before applying the formatting. It would treat your intent as part of the rule, not an afterthought. Until that becomes the norm, the lesson is that every rule needs a precise edge case. Test the boundaries, not just the middle values. And if a number between 5% and 10% shows up green, check whether your "green" rule actually stops at 5% or just pretends to.