Conditional Logic & Validation
Conditional Formatting: Highlight an Entire Row or Column Based on Another Cell ($C2 vs C$2)
Master mixed cell references in custom conditional-formatting rules: lock the column ($C2="Overdue") to highlight an entire row across A2:E20, or lock the row (B$2="Blocked") to highlight an entire column.
Why =C2="Overdue" only colors column C while =$C2="Overdue" highlights the entire row!
When you apply a formula-based Conditional Formatting rule to a multi-column selection such as A2:D5 (starting from active cell A2), the spreadsheet evaluates your rule for every cell in the block by shifting relative coordinates! If you write =C2="Overdue" without a dollar sign, cell A2 checks C2, cell B2 checks D2, and cell C2 checks E2—breaking the row highlight completely!
Prepending a column-locking dollar sign =$C2="Overdue" (mixed column-absolute, row-relative reference) pins every column A, B, C, and D to inspect Status cell $C2 while still letting row 2 step down to $C3, $C4, and $C5! Conversely, locking the row number =B$2="Blocked" pins every row in column B to inspect header/status row 2 so an entire vertical column highlights together! Testing the exact boolean rule in a helper column first guarantees 100% predictable row formatting.
| Formatting Goal on Selection A2:D20 | Custom Rule Written for Top-Left Cell A2 | Why the Dollar Sign Placement Works |
|---|---|---|
| Highlight Entire Row when Status (Col C) = "Overdue" | =$C2 = "Overdue" | Locks column $C across cols A:D; row 2 increments down rows 2:20 |
| Highlight Entire Row when Balance > 0 AND Overdue | =AND($C2 = "Overdue", $D2 > 0) | Combines two column-locked conditions on the same row |
| Highlight Entire Column when Row 2 Flag = "Freeze" | =A$2 = "Freeze" | Locks row $2 down all rows; column A shifts across cols A:D |
Row-Highlight Boolean Rule =AND(TRIM($C2) = "Overdue", $D2 > 0)
Returns TRUE across every column of row 2 whenever column C equals "Overdue" and unpaid balance $D2 is strictly positive.
=AND(TRIM($C2) = "Overdue", $D2 > 0)Count of Rows Highlighted Across the Table =COUNTIFS(C2:C5, "Overdue", D2:D5, ">0")
Counts how many entire invoice rows currently satisfy the multi-column row-highlight rule.
=COUNTIFS(C2:C5, "Overdue", D2:D5, ">0")Try the example
Use the original values or change them to test your understanding.