You need to visually flag tasks or projects that are overdue in your Excel sheet. Manually checking dates is slow and error-prone. Excel’s Conditional Formatting can automatically color rows based on a date column. This article shows you how to use the TODAY function to highlight any row where the deadline has passed.
Key Takeaways: Automatically Highlight Overdue Items
- Conditional Formatting > New Rule > Use a formula: Applies formatting to entire rows based on a logical test comparing a date to TODAY().
- TODAY() function: Returns the current date from your system clock, updating the formatting automatically each day.
- Apply formatting to =$A:$Z: Uses a mixed reference to lock the column check but apply the rule across all columns in the row.
Using Conditional Formatting With TODAY for Deadlines
Conditional Formatting changes a cell’s appearance based on a condition you set. The TODAY function is a volatile function, meaning it recalculates every time the worksheet recalculates. This ensures your deadline highlights are always current. The core logic is a simple date comparison: you check if the date in your deadline column is less than the current date returned by TODAY(). When you set this up as a rule for an entire row, Excel can apply a fill color, font color, or border to make overdue items stand out immediately.
Steps to Apply Row-Based Deadline Formatting
- Select your data range
Click the header of the first column in your data table and drag to select all columns and rows you want the rule to apply to. For example, select from column A to column E for all your tasks. - Open the Conditional Formatting menu
Go to the Home tab on the ribbon. In the Styles group, click Conditional Formatting. From the dropdown menu, select New Rule. - Choose the formula rule type
In the New Formatting Rule dialog box, select the last option: ‘Use a formula to determine which cells to format’. - Enter the comparison formula
In the formula field, type your rule. Assuming your deadline dates are in column C, and you selected all rows from row 2 onward, use this formula: =$C2<TODAY(). The dollar sign before the C locks the column reference. - Set the visual format
Click the Format button. In the Format Cells dialog, go to the Fill tab and choose a highlight color like light red. You can also set font styles on the Font tab. Click OK to close the Format Cells dialog. - Finalize and apply the rule
Back in the New Formatting Rule dialog, review the preview. Click OK to apply the rule. All rows where the date in column C is in the past will now be highlighted.
Common Mistakes and Formatting Limitations
Formula Does Not Highlight the Correct Rows
This is almost always due to an incorrect cell reference in the formula. If your selected range started at row 1, your formula must reference the first row of that range, like =$C1<TODAY(). If you started at row 5, use =$C5<TODAY(). The row number must match the first row of your selected range. Also, ensure your deadline column cells contain real Excel dates, not text that looks like a date.
Highlighting Disappears or Doesn’t Update
Excel needs to recalculate to update the TODAY function. This happens automatically when you open the file or edit a cell. If highlights seem stale, you can force a recalculation by pressing F9. Also, check that workbook calculation is set to automatic via File > Options > Formulas > Workbook Calculation > Automatic.
Only a Single Cell is Highlighted, Not the Whole Row
This happens if you applied the rule to only a single column instead of the entire row range. Delete the rule and reapply it, making sure you select all columns for your data rows before creating the new rule. The formula’s column lock (the $ before the column letter) is what allows the formatting to span across the entire selected row.
Conditional Formatting Rule Types for Dates
| Item | Highlight Dates Before Today | Highlight Dates Within Next 7 Days |
|---|---|---|
| Primary Use | Identify overdue items or past events | Flag upcoming deadlines or tasks due soon |
| Example Formula | =$C2<TODAY() | =AND($C2>=TODAY(), $C2<=TODAY()+7) |
| Formatting Suggestion | Solid red fill | Light yellow or orange fill |
| Reference Locking | Absolute column ($C), relative row (2) | Absolute column ($C), relative row (2) |
| Recalculation Trigger | Worksheet recalculation (TODAY function) | Worksheet recalculation (TODAY function) |
You can now automatically highlight any row where a deadline has passed. Use the formula =$C2<TODAY() with a red fill to make overdue items obvious. Try creating a second rule with a yellow fill for items due within the next week using the AND function. For managing multiple rules, use Home > Conditional Formatting > Manage Rules to change their order of application.