Conditional formatting with a formula: highlight the row, past-due dates, blanks, duplicates, weekends

The one rule to understand

Select A8:H157. Add a rule → Use a formula (Excel) or Custom formula is (Sheets). Write the formula as if for A8, the top-left cell. The app evaluates it for A8, then for B8 with every relative reference shifted one column, then for A9 with them shifted one row, and so on.

So =$G8="Done" on A8:H157 colours the whole row 8 when G8 is Done, the whole row 9 when G9 is — because the $ pins the column to G while the row slides. Without the $, cell B8 would test H8, C8 would test I8, and the effect falls apart to the right of G.

=$G8="Done"      column pinned, row slides   → colour the row by one cell in it
=B$7=TODAY()     row pinned, column slides   → colour the column by its header
=$B$4="Sunday"   both pinned                 → the same test for every cell (a switch)

Highlight the row by a status

=$G8="Done"                          grey, strike-through — Stop If True
=$G8="Overdue"                       red fill
=OR($G8="Today",$G8="Soon")          yellow fill

This is what “conditional formatting based on another cell” means in practice: the rule lives on the whole row, but the condition reads one column — $G locked, the row number left free to slide. Lock the column and not the row, and the colour follows the status cell across every cell in the row.

Rules apply top to bottom; the first that’s true wins for a property, and Stop If True (Excel) prevents later rules from adding their formatting. Sheets has no Stop If True — the first matching rule wins outright, so put the specific one first.

Past-due dates, and “within a week”

=AND($E8<>"", $E8<TODAY(), $F8="")      due date past, not yet ticked done
=AND($E8<>"", $E8<=TODAY()+7)             due within a week
=AND($E8<>"", $E8<$C$3)                   against an as-of date in C3 instead of TODAY()

The $E8<>"" guard matters: an empty cell compares as 0, and 0 is before every date, so without it every blank row lights up as overdue.

Both rules are arithmetic on a day count — TODAY()+7 is a week from now because adding 1 adds a day. The rest of that arithmetic, including the status chain these colours sit on top of, is in the date formulas guide.

Blank cells, and cells that only look blank

=$B8=""            blank, or a formula returning ""
=ISBLANK($B8)      truly empty — a formula returning "" is NOT blank
=LEN(TRIM($B8))=0  blank or only spaces

For “required field missing” on input rows, =AND($B8<>"", $E8="") — has a task, no due date.

Duplicates

=COUNTIF($A$5:$A5, $A5)>1          expanding range: only repeats are marked, the first stays clean
=COUNTIF($A$5:$A$400, $A5)>1       every occurrence, including the first

The expanding-range form is the useful one on a list you’re cleaning: fix the marked rows and the marks disappear.

Weekends and today in a calendar

In a grid where every cell is a real date (our calendar and planner are built that way):

=WEEKDAY(B8,2)>5                      Saturday or Sunday → shaded
=B8=TODAY()                            today → blue
=MONTH(B8)<>$B$4                       a day from the neighbouring month → grey text, Stop If True
=COUNTIF(hol_dates,B8)>0               a date in the holidays list → pink

No $ on B8 in the first three: every cell tests its own date. When the grid is a header row of dates with data below it — a Gantt chart, a week planner — pin the row instead: =WEEKDAY(B$7,2)>5 shades the whole column under a weekend header.

A Gantt bar is three rules

=AND(J$5<=$E8, J$5+6>=$D8)                                   the week overlaps the task → bar
=AND(J$5<=$E8, J$5+6>=$D8, J$5 < $D8+($E8-$D8+1)*$G8)         within the done % → darker
=AND(J$5<=TODAY(), J$5+6>=TODAY())                            this week → a line

Mixed anchoring does the work: J$5 slides across the week columns and stays on the header row; $D8 slides down the task rows and stays on the start column. The bar is nothing but formatting — no shapes, no add-in, and it moves when the dates change.

Values from another sheet

Excel 2007 refuses a formula rule that references another sheet directly. Two ways around it, both of which also read better: a named range (=COUNTIF(hol_dates,B8)>0 where hol_dates points at Setup), or a helper column on the same sheet that pulls the value in. Google Sheets needs INDIRECT("Setup!A8:A47") for cross-sheet references in rules — another reason to prefer named ranges.

Data bars and colour scales

The built-in bar and scale rules (Excel: Data Bars; Sheets: Color scale) aren’t formula rules, but they combine with them: a data bar on a Progress column, plus a formula rule that greys the row when done. Set the bar’s minimum to 0 and its maximum to a fixed number (1 for percentages), or a 20 % bar looks full on a sheet where 20 % is the top.

Excel vs Google Sheets

Copying a rule, removing one, and the ranges that drift

A rule belongs to a range, not to a cell, and that is where most of the trouble comes from.

Copy it to another range. Copy a cell that has the rule, then paste formats only — Excel: Paste Special → Formats; Sheets: Paste special → Format only. The format painter does the same for one drag. The formula re-anchors exactly as the $ signs say, which is why getting the anchoring right first saves the second hour.

Watch the range fragment. In Excel, inserting rows and pasting around a range splits one rule into a dozen — $A$8:$H$40, $A$41:$H$41, $A$42:$H$157 — each a separate line in Manage Rules, and only some of them updated when you next edit the formula. If a colour stops halfway down a column, open Manage Rules and look at the Applies to box before touching the formula: fix the range, not the condition. Re-entering the full range in that box merges them back.

Remove. Excel: Conditional Formatting → Clear Rules (from the selection or the whole sheet). Sheets: Format → Conditional formatting, then the bin on the rule. Clearing the formatting with Clear → Formats does not remove the rule in Excel — it comes straight back on the next recalculation, which is the usual reason a colour “will not go away”.

Checking rules you didn’t write

Excel: Conditional Formatting → Manage Rules → This Worksheet lists every rule with its range and formula. Sheets: Format → Conditional formatting shows the rules for the selected range. The Formulas sheet in each of our Pro workbooks lists the rules in words next to the formulas, which is faster than the dialog when you want to know why a cell is yellow.