Try the example, then check the result.
Download the fictional material and follow the steps below. The video includes 3 marked screenshots from the English app, with illustrative diagrams for the other steps. Product controls may vary by version.
Check the answer before applying the method to your own data. How we review guides ↗
Watch the complete lesson.
The video includes 3 marked screenshots from the English app, with illustrative diagrams for the other steps. English captions. Product menus may differ in your account.
This Excel for the web exercise uses six fictional tasks. A conditional formatting rule should color only rows with a real date before today and a status other than Done. Use the tab-separated task board to build the rule yourself, or open the practice workbook with the rule already applied to inspect it. The independent answer check identifies exactly two rows that should turn red. Both practice files use dates relative to the current day. The prebuilt workbook was also opened in English Excel for the web and displayed that two-row result.
Prepare the task board
- To build the rule yourself, open a disposable blank workbook in Excel for the web. Open
task-board-paste.txt, select its contents, and paste into cell A1. It has three columns for the fictional task, calculated due date, and completion status. The separateoverdue-tasks-practice.xlsxis a finished reference copy; its rule already covers A2:C7, so skip rule creation if you use it for inspection. Do not overwrite a real task list. - Check that the Due date formulas in B2, B3, B5, B6, and B7 evaluate to dates. B4 must remain truly blank. If pasted formulas remain text, re-enter those five formulas directly in Excel. Display the due-date column as Date if needed.
=ISNUMBER(B2)should return TRUE for a valid date formula;=ISNUMBER(B4)should return FALSE. - Before formatting, read the six rows. Only Send draft and Fix invoice are past due and not Done. Budget review is future, Request sign-off is blank, Archive report is Done, and Publish notes is due today.
Add the formula rule
- Select A2:C7, excluding the header. In Excel for the web, open Home → Styles → Conditional Formatting → New Rule. Confirm Apply to range is
A2:C7; choose Formula as the rule type. Microsoft's web instructions show these controls, though layout may vary by account and version. - Enter this exact formula and choose a readable light red fill:
=AND(ISNUMBER($B2),$B2<TODAY(),$C2<>"Done")The dollar signs hold columns B and C while the row number changes as the rule moves down. ISNUMBER rejects the empty due date, <TODAY() excludes today and future dates, and <>"Done" excludes completed work. When the prebuilt XLSX was opened in Excel for the web, its rule editor showed the equivalent relative-reference form =AND(ISNUMBER(RC2),RC2<TODAY(),RC3<>"Done"): RC2 refers to column B and RC3 to column C on each current row. Keep the rule's Apply to range at A2:C7.
- Finish the rule. Confirm again that it applies to A2:C7, not only
B2:B7and not the header row.
Check the result and reactiveness
Compare the board with the answer check: exactly Send draft and Fix invoice should have the fill. The supplied XLSX stores the same formula rule on A2:C7 and serves as a comparison if your new rule differs. On a disposable copy, temporarily change Fix invoice from In progress to Done: its fill should disappear. Restore In progress, clear B7, and check the fill disappears again. Restore =TODAY()-1 afterward. These edits test the two safeguards, not just the initial appearance.
| Symptom | Likely check |
|---|---|
| Blank due dates color red | Add or correct ISNUMBER($B2). |
| A Done task colors red | Confirm the status column is C and the text is exactly Done. |
| Only due-date cells color red | Change Apply to range to A2:C7. |
| No tasks color red | Confirm the pasted date formulas evaluate numerically and the formula starts on row 2. |
| A task due today colors red | Use <TODAY() rather than <=TODAY(). |
Swipe or scroll sideways to see every table column →
FAQ
Why use a formula rule? A built-in date rule cannot express the blank-date and status checks together. Microsoft's Formula rule evaluates TRUE or FALSE for each selected cell.
Will the practice dates go stale? The supplied date cells use TODAY() offsets. Their displayed dates change, but the expected relative cases stay the same.
Does a blank Due date count as zero? The rule explicitly rejects it with ISNUMBER, so it will not be highlighted even if a simple comparison would misbehave.
Sources and visual note
Microsoft's conditional formatting guide for Excel, including the web tab and conditional formula guidance were reviewed on 9 October 2026. The supplied XLSX was inspected locally: cells B2, B3, B5, B6, and B7 contain TODAY() formulas, B4 is blank, and one native conditional-formatting rule applies =AND(ISNUMBER($B2),$B2<TODAY(),$C2<>"Done") to A2:C7. In a private fictional copy opened in English Excel for the web on 9 October 2026, the rule manager showed one rule on A2:C7; Send draft (7 October) and Fix invoice (8 October) were highlighted, while the future, blank, Done, and due-today rows stayed clear. Three genuine app captures are recorded for scenes 4, 5, and 7; the companion video includes them. Other panels are illustrative diagrams. Creating the rule from a blank sheet and temporarily changing the two boundary cells were not part of that live check.
Practice downloads.
The exercise files contain invented information. Save a local copy before editing.