This is a spreadsheet horror story, and it is far more common than most users realize. Our take is plain: if your conditional formatting rules are rewriting themselves, you are not dealing with a glitch, you are dealing with a fundamental flaw in how legacy spreadsheet tools handle dynamic data. The user who posted this, LearningPenguin, did everything right: they used the latest Office 365, saved to OneDrive, and set a clean rule for A2:B200. Yet weeks later, that rule had mutated into an incomprehensible string of scattered cell references. The problem is not their file. The problem is the tool.
What happened here is that Excel's conditional formatting engine, when faced with row insertions, deletions, or cut-and-paste operations, does not always update the range reference cleanly. Instead of preserving the intended range, it splices together fragments from wherever data has moved. The result is a rule that applies to a chaotic patchwork of cells, A21, B67, B63, and so on, instead of the original block. For any user managing a growing dataset, this is not an edge case. It is a ticking clock. Every time you add rows or reorganize columns, you risk corrupting your formatting logic without any warning. The spreadsheet will not tell you the rule broke. It will simply stop highlighting the right cells, and you will discover the mess only when your data no longer makes visual sense.
This is where the conversation needs to shift. The instinct is to ask, "How do I prevent this?" and the honest answer from traditional tools is: you cannot fully prevent it. You can minimize the risk by using structured table references or avoiding manual row inserts, but those workarounds are fragile and require constant vigilance. The deeper issue is that conditional formatting, like so many features in legacy spreadsheets, was designed for static grids, not living datasets. When a spreadsheet is a living document, shared, edited, and grown over weeks, the rules that rely on fixed cell ranges become liabilities. The tool treats the spreadsheet as a fixed map, but your data is a moving city.
What users like LearningPenguin need is a system that understands intent, not just coordinates. An AI-native spreadsheet does not lock formatting to brittle cell addresses. It watches how your data evolves, preserves the logic behind your rules, and adjusts the range as your table grows. It flags the moment a rule might break, rather than letting it silently corrupt. The solution is not to memorize a list of manual precautions. It is to demand a tool that treats your formatting rules as what they are: a declaration of how your data should behave, not a fragile string of dollar signs and colons. Stop patching the old system. Start asking for one that works the way you do.