Xochitl tracks attendance for 640 students in Oaxaca. Her first version coloured every cell on a scale, which made a sheet that looked serious and told nobody anything, because when everything is coloured nothing is marked.
The version that works colours 23 cells out of 12,800. Those 23 are the students below 85 per cent for two consecutive months, which is the only thing anybody acts on.
The rule that reads another cell
Highlight Cell Rules colours a cell by its own value, which handles the simple cases and none of the useful ones.
A2:H641 selection
Single-cell formatting versus mixed-reference row highlighting
Highlight Cell Rules checks each cell in isolation, while a mixed-reference formula highlights entire rows from key columns.
Locking the column while leaving the row free ($D2) allows a single rule to format all eight columns of an underperforming student record.
The one worth learning is Use a formula to determine which cells to format. The formula is written for the top-left cell of the selection and applied to the rest by exactly the reference rules that govern any other formula.
Xochitl's is =AND($D2<0.85,$E2<0.85), with the selection covering A2:H641. The dollar signs before D and E lock the columns while the row moves, so every column of the row is tested against columns D and E, and the whole row lights up from one condition.
That single mixed-reference trick is what separates conditional formatting that marks rows from conditional formatting that marks cells, and it is the same $A2 form that builds a rate card.
What to colour, and how much
One rule, one colour, one meaning. A sheet with five colours needs a key, and a sheet needing a key is a sheet nobody reads at speed.
Visual signal restraint
Three design safeguards that keep conditional formatting readable
When every cell on a sheet receives colour, the sheet loses contrast and communicates nothing to the reader.
- One colour per purpose+Rainbow sheets that demand an explanatory key→Instant visual comprehension at high reading speed
- Highlight exceptions only+Searching for missing signals among thousands of coloured cells→Twenty-three red cells stand out immediately against pale backgrounds
- Pair colour with secondary markers+Illegibility for colour-blind readers and black-and-white printouts→Combining fill colour with bold text or borders ensures universal clarity
Roughly one in twelve men experiences colour vision deficiency, making multi-attribute formatting a necessary standard rather than an optional polish.
Colour the exception rather than the norm. Twenty-three red cells among 12,800 pale ones is instantly readable; the inverse, where the acceptable ones are green, means the eye has to search for absence.
And never colour alone. Roughly one in twelve men has some colour vision deficiency, so a rule that also applies bold, or an icon, or a border, survives being printed in grey and survives being read by everybody.
The three built-in sets, and what each is for
Data bars put a small bar inside the cell, which works when the question is how big and the numbers are all positive. They stop being honest when negatives are included, because the baseline moves to the middle of the cell.
Six students, six months
A colour scale across a whole grid, and the two rows a rule would mark
Every cell here is shaded, which is what a colour scale does. Only two students meet the rule that matters, and on a scale they are not the darkest cells on the page.
attendance, per cent of sessions, one term
On this scale students 204 and 555 are pale rather than flagged, which is the argument of the page: a scale shows shape across a grid, and a rule reading two columns marks the 23 rows out of 640 that anybody acts on.
Colour scales are for a grid where the pattern matters more than any single value: 640 students by 12 months, where the eye is looking for a block rather than a row. This is the one Xochitl overused, and it is the right tool exactly when the reader is scanning for shape.
Icon sets carry a category rather than a magnitude, and their default thresholds are percentiles rather than values, which means the same icon means something different every month unless the thresholds are set by hand.
The three ways a rule stops working
Copy and paste. Pasting a cell carries its formatting rules with it, so a sheet accumulates near-duplicate rules until one of them contradicts another. Manage Rules shows the list, and a sheet with fourteen rules where three were intended is the normal state of an old workbook.
Rule maintenance
Three mechanisms that corrupt conditional formatting over time
Old workbooks frequently accumulate overlapping rules that contradict each other or fail to cover new rows.
Conditional formatting breakdown
- Clipboard rule duplicationPasting cells copies formatting rules into the sheet until redundant rules conflict
- Range boundary gapsRules applied to A2:H641 fail to expand when row 642 is inserted unless using Ctrl+T
- Evaluation order conflictsRules evaluate top-down; an unconfigured Stop If True setting lets lower rules overwrite intended highlights
Opening Manage Rules once a year reveals whether an inherited workbook has accumulated dozens of duplicate rules across fragmented ranges.
Rows inserted outside the applied range. A rule applied to A2:H641 does not extend to row 642. Applying rules to a table created with Ctrl+T fixes this, because the range grows.
And rule order. Rules are evaluated top to bottom and the first match can be told to stop the rest with Stop If True. Two overlapping rules produce a result that depends entirely on which is listed first, which is invisible until somebody reorders them.
Finding the exceptions without colour at all
Worth saying, because conditional formatting is often used where a filter is better. If the answer is a list of 23 students, filter to them and produce the list. Colour is for when the exception has to be seen in context, beside everything it is not.
Operational context
Filtering out records versus highlighting exceptions in context
Conditional formatting is often misapplied where a basic data filter provides a cleaner, faster outcome.
- Apply a data filterWhen the goal is an actionable list
- Isolates the 23 students requiring intervention directly
- Hides acceptable rows to reduce visual noise
- Provides instant export and follow-up rosters
- Apply conditional formattingWhen context must remain visible
- Retains full 640-student roster on the screen
- Shows how outliers sit relative to peers
- Preserves layout structure for multi-metric review
Tool selection
If the end result is a targeted contact list, filtering is immediate; colour is reserved for times when outliers must be evaluated alongside normal records.
What to use, as of 2026
Home, then Conditional Formatting, then New Rule, then Use a formula, in every version since Excel 2007. It replaced a helper column of TRUE and FALSE that somebody then coloured by hand.
Modern rule architecture
Three structural tools that ensure conditional rules scale cleanly
Combining formula rules with dynamic tables prevents range decay as new rows are added.
- Formula-based rulesUse a formula option with mixed references ($D2) to govern entire rows with precision
- Manage Rules interfaceCentral manager to audit applied ranges, resolve conflicts, and purge legacy duplicates
- Ctrl+T structured tablesDynamic table boundaries expand formatting ranges automatically as records exceed row 641
Excel workbook environment
Modern AI assistants write mixed-reference formulas accurately when given starting coordinates, but managing accumulated rule bloat remains a human task.
Manage Rules, in the same menu, which lists every rule on the sheet with its range and its order. This is where the fourteen accumulated rules become visible, and reviewing it once a year on an inherited workbook is worth ten minutes.
Ctrl+T tables underneath, so a rule's range grows with the data instead of ending at row 641.
A model will write the formula for a stated condition, free on every major assistant, and it gets the mixed references right when told the selection starts at A2. It cannot see how many rules a workbook has already accumulated, and that is what makes an old sheet colour things nobody asked for.
The mixed reference the whole-row rule depends on is cells, references and the interface, and choosing between colouring in place and drawing the summary is choosing one, and formatting it honestly. Counting the exceptions rather than colouring them is summarising thousands of rows in seconds, under the most used analysis tool in the world.