Conditional Formatting
Home > Conditional Formatting offers three kinds of rule, and only one of them — Use a formula — can colour a cell based on what another cell contains. Highlight Cells Rules and Top/Bottom Rules judge each cell by its own value and need no formula whatsoever. Data Bars, Color Scales and Icon Sets rank every value in the selection against the rest of that selection, so they need no rule either. Use a formula is where the work lives: the formula is written once, for the top-left cell of Applies to, then copied across the range — which is why the $ signs decide whether a rule paints one cell or a whole row.
The formula
=$A2="Overdue" whole row lights up: $ pins the column, the row number moves
=A2="Overdue" column A only: with no $ the test drifts to B2, C2, D2 as it copies
=WEEKDAY($A2,2)>5 rows whose date in column A falls on a Saturday or Sunday
=AND($C2<>"",$C2<TODAY()) rows whose due date in column C has passed, blanks left aloneA worked example
An order table in A2:D100 with the status text in column C, where every row of an overdue order should shade, not just the C cell.
Home > Conditional Formatting > New Rule > Use a formula, enter =$C2="Overdue", and set Applies to = $A$2:$D$100
Rows whose column C reads Overdue shade across all four columns, and editing C7 to Shipped clears row 7 straight away. Dropping the $ breaks it in a way that looks random: written for the top-left cell A2, =C2="Overdue" becomes =D2="Overdue" in column B and =E2="Overdue" in column C, so each column ends up judged by a different, unrelated cell.
Which one do I need?
| If you want to… | Use |
|---|---|
| A cell should change colour because of its own value — over 100, contains "urgent", dated this month | Highlight Cells Rules or Top/Bottom Rules, which take a value rather than a formula |
| You want to see at a glance how the values in a column compare | Data Bars, Color Scales or Icon Sets — they rank the selection against itself, with no rule to write |
| The colour depends on a different cell from the one being coloured | New Rule > Use a formula, with the tested column pinned: =$C2="Overdue" rather than =C2="Overdue" |
| The entire row should highlight, not only the matching cell | Widen Applies to across every column of the row, and put a $ in front of the tested column letter only |
| Duplicate values need flagging | Highlight Cells Rules > Duplicate Values does it with no formula at all |
| Every other row should be shaded for readability | A formula rule of =MOD(ROW(),2)=0 works, but converting the range to a Table gives banded rows with no rule to maintain |
| The colours appear on the wrong cells | Open Manage Rules and read Applies to first — it drifts when rows are inserted or ranges pasted, and one rule often fragments into many |
| The rule does nothing whatsoever | The tested values are usually text that merely looks like numbers or dates, so every > or < comparison fails silently; convert them first |
| The formatting needs to go | Home > Conditional Formatting > Clear Rules, choosing between the selection and the whole sheet — deleting cell contents leaves the rules behind |
| The same rule is needed on another range | Format Painter carries it across, after which Manage Rules can merge the two Applies to ranges back into one rule |
Frequently asked questions
Why does my conditional formatting highlight the wrong column?
The rule is stored once, for the top-left cell of its Applies to range, and Excel shifts the references as it evaluates every other cell — exactly as copying a formula does. Put a $ before the column letter of the cell being tested, as in =$C2, and the test stays on column C for every column it paints.
How do I highlight an entire row based on one cell?
Two settings have to be right together. Applies to must span all the columns of the row, such as $A$2:$D$100, and the rule must pin the tested column with a $ while leaving the row number free, as in =$C2="Overdue". Pinning the row as well, =$C$2, tests one fixed cell and colours either everything or nothing.
Why has one rule turned into dozens in Manage Rules?
Copying, pasting and inserting rows inside a formatted range makes Excel split the Applies to range into fragments, each carrying its own copy of the rule. They behave identically until one of them drifts. Delete the duplicates in Manage Rules, then widen the surviving rule back over the whole range.
My conditional formatting is not working at all — what should I check?
Open Manage Rules and set its dropdown to This Worksheet, because the rule may not cover the cells you are staring at. Then check whether Stop If True on a rule above is cutting off the ones below it, and whether the values are text rather than real numbers or dates, which makes every comparison fail without an error.
New guides and tools, once a month
DE + EN · double opt-in · no spam