Conditional formatting that highlights what matters

TL;DR:

• Conditional formatting changes how a cell looks when it meets a rule, so the sheet points at what needs attention.

• Date rules and custom formulas cover most real cases, such as overdue and not done.

• Use one or two colors. If everything is highlighted, nothing is.

A task list with forty rows is hard to scan. You want the late ones to jump out without sorting or reading every date. Conditional formatting does that: you write a rule, and any cell that meets it changes color automatically, now and whenever the data changes.

This guide builds one useful rule in Excel and Google Sheets, then shows how to keep a sheet from turning into a rainbow.

What it can and cannot do

A rule has two parts: a test and a look. The test is something like, the cell is a date before today, or the cell contains the word Blocked. The look is a fill color, a font color or bold. When the test is true, the look applies. When it stops being true, the look goes away.

It only changes appearance. It does not move, delete or sort anything, and a colored cell is still the same value underneath. That also means that colors do not carry over into a pivot table or a formula.

The built-in rules, and when they are enough

Both apps ship simple rules: cells greater than a number, text containing a word, duplicates, and rules based on dates such as Yesterday, Today or Last 7 days. The date rules are the quickest place to start. Select the due-date column, open the conditional formatting menu (Home tab in Excel, Format menu in Google Sheets), and choose a date-based rule.

The menu names move around between versions, so look for the words conditional formatting or highlight cells rules rather than memorizing a path.

Built-in date rules look only at the one cell. They cannot say, only if the status is not Done, because that needs a second column. For that you need a custom formula.

Writing a custom formula rule

Choose the custom formula option (Use a formula to determine which cells to format in Excel, Custom formula is in Google Sheets). The formula must return TRUE or FALSE. Write it as if for the first row of the range, and the app applies it to every row.

1. Select the whole range: Highlight the cells you want to color, for example A2:D41, starting from the top-left data cell.

2. Open the custom formula option: Pick the rule type that lets you type a formula.

3. Write the test for the first row: Use $ in front of the column letter to lock the column and leave the row number free, for example =$C2<TODAY(). The $ stops the rule from sliding sideways, so every cell in the row looks at column C.

4. Choose the look: Pick one fill color and apply it.

5. Check a few rows by eye: Compare the colored rows against the dates yourself before you trust it.

Worked example: highlighting overdue tasks

Say your task sheet has Task in A, Owner in B, Due date in C and Status in D. A task is overdue if its due date is before today and its status is not Done. We will evaluate it as if today is 12 November 2026.

The formula for the first data row (row 2), applied to A2:D6, is:

=AND($C2<TODAY(), $D2<>"Done")

AND needs both tests to be true. $C2<TODAY() is true when the due date is strictly earlier than today. $D2<>"Done" is true when the status is anything other than Done. Row 2 (Send invoice) gives TRUE and TRUE, so it is colored. Book venue is due today, so C<TODAY() is FALSE and it stays plain. Draft report fails the second test. Only Send invoice and Order supplies end up highlighted, which matches the table.

The rule is live. Tomorrow, Book venue will turn highlighted automatically if it is still open, because TODAY moves forward. If dates are typed as text such as 5 Nov, the test will not work, so make sure the due-date column holds real dates.

Restraint with color

A highlight works because it is rare. Decide what single thing a viewer should notice first, and give that one color. A pale red fill with dark text for overdue is enough. If you add a second rule, say amber for due within three days (a formula such as =AND($C2>=TODAY(), $C2<=TODAY()+3)), keep it clearly lighter.

• Two or three colors at most, and each one means one thing.

• Use pale fills with readable dark text, not saturated fills.

• Do not rely on color alone. Someone who cannot tell red from green, or who prints in black and white, loses the signal. If it matters, add a helper column that says LATE in words.

• Write down what the colors mean in a note at the top of the sheet.

When the rule does not work

• The whole sheet is colored. You probably forgot the $ or applied a formula meant for one row to a different starting row. The formula should be written for the top-left cell of the selected range.

• Nothing is colored. Test the formula in a spare cell. If it shows TRUE for a late task, the rule range is wrong. If it shows FALSE, the dates are text.

• Rules fight each other. Both apps let you open the list of rules and reorder them. Rules higher in the list win when they conflict.

• Copying rows adds rules. Pasting can split one rule into many pieces. Open the rules list now and then and tidy it.

A quicker route

If you are not sure where a menu sits in your version, DuckyHelper can see your screen while the pill is open, so you can ask out loud, and it answers while you keep working. The rule itself is the same in any app, so learn the test and the look, and the menus take care of themselves.

Frequently asked questions

Does conditional formatting slow down a big sheet?

Many rules over very large ranges can make a sheet slower, so apply rules to the range you need, not to entire columns, when your file gets heavy.

Can I highlight the whole row instead of one cell?

Yes. Select every column of the table and use a formula that locks the column with $, as in the example. The test looks at column C, and the color applies across the row.