Skip to content
Reading settings
ExcelIntermediate~30 min practiceLesson 19 of 34

Highlight exceptions with a clear rule

Highlight overdue tasks and add a text label for the same condition.

Content reviewed · How we check lessons

Loading your progress…

In this lesson

Build a row rule using a fixed review date and check that new tasks are included.

Example

What still needs action on April 10?

Enter ID, Task, Due and Status in A1:D4: T01 Inventory count, April 3 2026, Open; T02 Printer check, April 12 2026, Open; T03 Budget review, April 5 2026, Done. Use DATE to enter the dates and set the review date to April 10, 2026.

How it works

A formatting formula uses the first row of its applied range as the starting point. Lock the Due and Status columns, but let the row number move so each record tests its own fields.

An overdue task needs a past due date and an unfinished status. Add a text label as well as color so the action remains clear to everyone.

Steps

  1. Enter the tasks. In C2:C4, use =DATE(2026,4,3), =DATE(2026,4,12) and =DATE(2026,4,5). Type ReviewDate in F1 and =DATE(2026,4,10) in F2.

  2. Select A2:D4. Choose Home > Conditional Formatting > New Rule and select the formula-based option where available. The first selected row is row 2.

  3. Enter the formula below and choose a readable highlight. $C and $D keep the rule on the Due and Status columns while row 2 moves down the range.

    Excel argument separator
    =AND($C2<$F$2,$D2<>"Done")

    English function names. Formula settings →

  4. Open Manage Rules and check Applies to covers A2:D4. T01 should be highlighted, T02 is not overdue and T03 is Done.

  5. Add an Exception column with =IF(AND(C2<$F$2,D2<>"Done"),"Overdue","No overdue action"). Fill it down to give each task a text status as well.

  6. Change T01 to Done on a copy. Check that its highlight is removed and the helper changes to No overdue action. Restore Open in the original exercise.

Try it yourself

  1. Add T04 Visitor briefing with due date April 9, 2026 and Status Open. Extend the applied range and helper formula to that new row rather than assuming every manual range expands.
  2. Create a second simple rule for Done and inspect rule order if formats overlap. Explain the rule purpose in a legend rather than using unexplained red and green cells.
Show expected result
  • Initially only T01 is an overdue unfinished task. After adding T04 and extending the ranges, T01 and T04 are the two overdue items.
  • T03 remains excluded despite its past due date because it is Done. Changing T01 to Done removes its outstanding-action flag.

If something goes wrong

The highlight appears one row away from the task.
Compare the formula’s starting row with Applies to. A range beginning on row 2 needs $C2 and $D2, not row 3.
A new task has no highlight or status.
Extend Applies to and fill the helper formula into the new row. Check both ranges after adding T04.

Quick check

Try these questions after the exercise. Check the result in your app before marking the lesson complete.

1. Which original task should be highlighted?
2. Why is the Due column written as $C2?
3. How many overdue unfinished tasks exist after adding T04?

Choose an answer for all 3 questions to check your understanding.

Optional: try it with Copilot

If you have Copilot, try this after the exercise. Keep the original file and compare its suggestions with your own result.

Check the overdue rule and Applies to range. Which tasks need action on April 10, 2026? Suggest a text label that matches the highlight.

More from Microsoft

These Microsoft guides cover the tools used in this lesson.

Loading your progress…