Single-color rules and color scales handle the obvious cases. Everything more interesting — highlight a whole row when one cell changes, color a deadline as it approaches, react to a value on another sheet — runs through one option: the custom formula. It formats a cell based on any expression that returns TRUE or FALSE, and learning to write those expressions is what separates basic conditional formatting from the kind that quietly runs a dashboard. This covers the custom-formula patterns worth knowing and the rule-priority behavior that decides which one wins.
How a custom formula rule thinks
Choose “Custom formula is” under Format cells if, and Google Sheets evaluates your formula once for every cell in the applied range. If it returns TRUE for a cell, that cell gets the format; if FALSE, it doesn’t. The trick that confuses everyone at first is what “the cell” means when the formula references a column — and the answer is all in the dollar signs.
=$B1="Done"Locking the column with $B while leaving the row free (1, no dollar) tells the rule: for every cell in the range, check that row’s column B. Row 5 checks $B5, row 6 checks $B6, and so on. That single asymmetry — column locked, row floating — is the foundation of almost every advanced rule below. Get it backwards and the formatting lands on the wrong cells, which is the source of nearly every “why is the wrong row colored?” question.
A quick way to internalize it: the dollar sign means “don’t move this part.” $B means always column B; a bare 2 means this row, whatever row it lands on. So =$B2 reads “column B, my own row” — which, evaluated across a range, checks each row’s own B cell. Once that clicks, every rule below is just a variation on the same idea, and you stop guessing at where to put the dollar signs.
Whole-row and cross-column highlights
The most useful custom formula formats an entire row based on one cell. Apply the rule to the full data range — say A2:F100, not just column B — and use the locked-column reference:
=$F2="Closed"Now every row whose column F reads “Closed” colors across all six columns, because the rule runs on each cell of the range and they all check the same F cell for their row. Cross-column logic works the same way: =$E2>$D2 highlights rows where the actual value beat the target, comparing two columns per row. You’re no longer formatting cells in isolation — you’re formatting rows by their meaning, which is what makes a long table readable at a glance. This pairs naturally with locking inputs through data validation so the values driving the colors stay clean.
Checkboxes make the pattern especially clean. Insert a checkbox column (Insert → Checkbox), and a rule of =$G2=TRUE highlights every row whose box is ticked. It turns a whole-row highlight into a one-click toggle — check the box and the row colors, uncheck it and the color clears. For a task list or an approval tracker, it’s the most satisfying two-second setup in Sheets, and it reads more naturally than a text status for anything that’s simply done-or-not.
Date math: overdue, upcoming, and aging
Dates are where custom formulas earn their keep, because the rule re-evaluates against TODAY() every day on its own. To flag overdue items:
=$C2<TODAY()Every row whose date in column C is in the past lights up, and tomorrow a newly-passed date joins them automatically. To catch deadlines coming up rather than already missed, bound it on both sides — due within the next seven days:
=AND($C2>=TODAY(), $C2<=TODAY()+7)The AND combines two date conditions so only the next week’s deadlines color. The same idea drives “aging” views — color a support ticket yellow after three days and red after seven by stacking two rules with different thresholds. Date-driven formatting turns a static list into something that updates its own urgency overnight, with nobody touching the sheet.
One refinement keeps aging rules honest: anchor them to a real date column, not a formula that recomputes. If column C holds the actual date you typed, the rule reads true history; if C is itself =TODAY() plus something, every row shifts daily and “overdue” never sticks to anything. Keep the date a fixed value and let the rule do the comparing — the rule is the live part, the data shouldn’t be.
React to another sheet with INDIRECT
A custom formula can’t reference another sheet directly — =Sheet2!A1="x" isn’t allowed in a formatting rule. INDIRECT is the workaround, building the reference from a text string the rule can resolve:
=$A2=INDIRECT("Settings!$B$1")This compares each row’s column A against a value held on a Settings tab — useful when a threshold or a “current period” lives in one place and several sheets should react to it. Change the value on Settings and every rule using it updates. It’s a way to make formatting respond to a central setting rather than a hard-coded value buried in each rule, and it keeps a shared workbook consistent when one number drives many views.
Rule order decides who wins
When several rules apply to the same cell, order matters. Google Sheets runs the rules top to bottom and stops at the first one whose condition is met — that rule’s format wins, and the rest are skipped for that cell.
This is why two correct-looking rules can fight: each works alone, but stacked in the wrong order one masks the other. When a color you expect doesn’t appear, check the rule order before the formula — it’s the more common culprit. The same precision you’d apply to an array formula belongs here too: a rule is only as good as the order it runs in.
Putting the advanced rules to work
=$B2="Done" as a normal formula and watch it return TRUE or FALSE. If the cell logic is right, the problem is the range or the rule order, not the formula.
The basics — single color, color scales — are covered in the guide to conditional formatting for reports. The advanced layer is all one feature deep: the custom formula, plus an understanding of how the dollar signs and the rule order behave. Master the locked-column reference, lean on date math for anything time-sensitive, reach for INDIRECT when a rule should follow a central setting, and keep your specific rules above your broad ones. Google’s own conditional formatting documentation lists every condition type when you need a reference. With those few patterns, conditional formatting stops being decoration and starts doing real work on every open. Build the rule once, get the dollar signs and the order right, and the sheet flags what matters for you from that point on.