Module 4 · Tables and rules that keep a sheet correct Lesson 12 of 15

Conditional formatting with formulas

The concept

The rule is a formula that returns TRUE or FALSE for the top-left cell, and Excel copies it down with the references shifted - which is why the $ matters.

Keep the number of rules small. Five rules a reader understands beat fifteen that produce a rainbow nobody can decode.

SpreadsheetCopy

Select the table -> Home -> Conditional Formatting -> New Rule -> Use a formula

= $F2 = "overdue"                    whole row red when status is overdue
= ISBLANK($G2)                       whole row yellow when the owner is blank
= AND($E2<TODAY(), $F2<>"closed")    overdue and still open

Worked example

Select the whole table and use =$F2="overdue" as the rule; the $ locks the column so each row is judged by its own status cell.

Your practice task

Highlight every whole row whose status says overdue, and every row whose owner is blank.

Do this before moving on. Reading a solution teaches the shape of it; typing it teaches you.

Key takeaway

The rule is a formula that returns TRUE or FALSE for the top-left cell, and Excel copies it down with the references shifted - which is why the $ matters.

Almost there

15 lessons left of 15

Finish every lesson to unlock the certificate. Each one ends in a practice task — do them and the final project brief writes itself.

Next recommended course Spoken English & Interview Communication