Google Sheets lesson 5 of 14, Data tools
Conditional formatting with custom formulas
Color cells, and whole rows, by any rule you can write as a formula.
Watch it done
4 stepsWatch the technique worked out on sample data, then try it yourself in the exercises.
How it works
Conditional formatting changes how a cell looks, not what it holds. Select the cells, open Format > Conditional formatting, and the "Conditional format rules" panel opens with your selection under "Apply to range". The "Format cells if…" list has ready-made rules such as "Greater than" and "Text contains". At the bottom of that list is Custom formula is, which takes any formula that comes out TRUE or FALSE. Pick a formatting style and click Done.
Write the custom formula for the first cell of the range. Sheets runs it again for every other cell, shifting the references exactly as if you had filled the formula across the range. With the rule applied to C2:C11, =C2>1500 checks C3 for the next cell, C4 for the one after, and so on.
Dollar signs decide what stays put. To compare every cell with a limit in E2, lock the limit: =C2>$E$2. To color whole rows, apply the rule to all the columns, such as A2:D10, and lock only the column you test: =$D2="Blocked". Every cell in row 2 then looks at D2, so the row lights up as one.
Because any formula works, the same idea covers duplicates, =COUNTIF($A$2:$A$12,A2)>1, and dates, =$C2<$F$2 for anything due before the date in F2. The format only changes the look. To count the highlighted rows, use the same test inside COUNTIF or COUNTIFS; Sheets has no built-in function that counts by color.
Syntax
=C2>$E$2Compare each cell with the limit in E2=$D2="Blocked"Applied to A2:D10: color the whole row=AND($C2<$F$2,$D2<>"Done")Due before the date in F2 and not done=COUNTIF($A$2:$A$12,A2)>1Values that appear more than once=$C2<TODAY()Dates before today, rechecked every dayWatch out for
- Write the formula for the first cell of "Apply to range". If the range starts at row 2 and the formula says C1, every cell checks the row above it.
- Without $ on the limit, =C2>E2 slides down to E3, E4 and so on, which are empty.
- For whole rows, lock the column ($D2) but not the row. $D$2 would make every row copy row 2.
- When two rules match the same cell, the one higher in the list sets the format. Drag the more specific rule to the top.
The exercises
- Amounts over the limit. Highlight the invoice amounts in C2:C11 that are above the limit in E2. The rule is applied to C2:C11: write its custom formula, and lock the reference to the limit.
- Past-due rows. Color the whole row of every task that is past due: its due date in column C is before the date in F2, and its status in column D isn't "Done". The rule is applied to A2:D10.
- Double bookings. Highlight every booking reference in A2:A12 that appears more than once. The rule is applied to A2:A12.
The practice grid runs in Sheets mode: FILTER, SORT, QUERY, ARRAYFORMULA, SPLIT and the REGEX functions behave as they do in Google Sheets, and empty results stay blank.
Amounts over the limit
C2:C11 that are above the limit in E2. The rule is applied to C2:C11: write its custom formula, and lock the reference to the limit.Conditional format rules
C2:C11Write it for the first cell, C2. Matching cells light up as you type.