Faragiri

Learn Conditional formatting Excel

A school attendance office in Oaxaca: the rule that reads other cells, why the whole row can be coloured by one column, and the three ways a rule stops working.

6Sections
4Min read
4Depth

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.

Highlight Cell RulesBuilt-in menu defaults
Formula rule ($D2)Mixed reference condition
Evaluation scopeEach cell tests only its own contentEvery cell in row tests against columns D and E
Visual outcomeScattered individual cells light up across the sheetThe entire row illuminates as a unified record
Condition complexityLimited to basic static comparisons like greater thanCombines conditions using =AND($D2<0.85,$E2<0.85)

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.

  1. One colour per purposeRainbow sheets that demand an explanatory keyInstant visual comprehension at high reading speed
  2. Highlight exceptions onlySearching for missing signals among thousands of coloured cellsTwenty-three red cells stand out immediately against pale backgrounds
  3. Pair colour with secondary markersIllegibility for colour-blind readers and black-and-white printoutsCombining fill colour with bold text or borders ensures universal clarity
Legibility principles

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

FebMarAprMayJun
Student 1189694979596
Student 2048884838684
Student 3379998979998
Student 4029190928991
Student 5558279818480
Student 6199596949795

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.

Maintainable formatting
  • 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.

Alongside this

Above this