Excel Highlight Row If Date Has Passed
To highlight an entire row in Excel when a date in that row has passed, use Conditional Formatting with a formula based on the date cell, such as =AND($C2<>"",$C2
How Conditional Formatting Works
Conditional Formatting is a built-in Excel feature that changes cell appearance (fill color, font, borders) based on rules you define. Unlike a static fill, it recalculates automatically whenever the workbook changes, so rows update as dates pass. The key is using a formula that returns TRUE or FALSE for each row; when TRUE, the formatting applies.
For row-level highlighting, the formula must use a mixed reference: lock the column letter with a dollar sign ($C2) but leave the row number relative (2). This way, Excel evaluates each row independently while always looking at the same column (e.g., column C for the date).
Step-by-Step: Highlight Rows with Past Dates
- Select the range of rows you want to format, for example
A2:E100. The active cell should be in row 2 (the first row of your selection). - Go to the Home tab, click Conditional Formatting, then New Rule.
- Choose Use a formula to determine which cells to format.
- In the formula box, enter (adjusting column C to your date column):
=AND($C2<>"", $C2 - Click Format, pick a fill color (e.g., light red) on the Fill tab, then click OK twice.
The AND function prevents blank cells from being highlighted, and TODAY() returns the current date. If you want to include today's date as "passed" (i.e., overdue as of today), use <=TODAY() instead of <TODAY().
Choosing the Right Date Column and Range
Your formula must reference the first row of your selected range. If your selection starts at row 2, use $C2; if it starts at row 5, use $C5. The column letter must match the column where your dates live. If dates are in column D, change $C2 to $D2.
Apply the rule to the entire data range, not just the date column. For example, if your table spans columns A through E, select A2:E100. The formula will evaluate each row's date and highlight all cells in that row when the condition is met.
Common Mistakes and Fixes
- Wrong row reference: If you select A2:E100 but write
$C1, the rule will shift and highlight the wrong rows. Always match the row number to the first selected row. - Absolute column but relative row: Using
$C$2would lock the row too, causing only row 2 to ever be highlighted. Use$C2(column locked, row free). - Date stored as text: If your dates are left-aligned text, the comparison fails. Use
DATEVALUEto convert, or re-enter dates as real dates (right-aligned by default). - Blank cells highlighted: Without the
<>""check, blank cells are treated as 0, which is less than today, so they'd all highlight. TheANDfixes this. - Rule order: If you have multiple conditional formatting rules, the first rule in the list takes priority when "Stop If True" is checked. Reorder rules via Conditional Formatting > Manage Rules.
Variations: Highlight Upcoming, Overdue, or Expiring Soon
You can adapt the same formula for other scenarios:
- Upcoming dates (future):
=AND($C2<>"", $C2>TODAY()) - Dates within the next 7 days:
=AND($C2<>"", $C2>TODAY(), $C2<=TODAY()+7) - Overdue by more than 30 days:
=AND($C2<>"", $C2<TODAY()-30) - Expired or expires today:
=AND($C2<>"", $C2<=TODAY())
For multiple conditions (e.g., red for overdue, yellow for expiring soon), create separate rules and manage their order. Each rule needs its own formula and format.
Performance and Large Datasets
Conditional formatting with formulas recalculates when the sheet changes. On very large sheets (tens of thousands of rows), this can slow down Excel. To improve performance:
- Limit the formatted range to only the rows with data, not entire columns.
- Avoid volatile functions like
NOW()(which includes time) if you only need dates;TODAY()is sufficient. - If the sheet becomes sluggish, consider using a helper column with a formula like
=IF($C2="","",IF($C2<TODAY(),"Overdue",""))and then apply a simple cell-value rule to that column.
Frequently Asked Questions
Why is my formula not highlighting any rows?
Check that the formula uses the correct column and that the row number matches the first row of your selection. Also ensure your dates are real dates (not text) and that the range you selected includes the rows you expect. Test the formula in a blank cell (e.g., =AND($C2<>"",$C2
How do I highlight a row if the date is within the next 30 days?
Use =AND($C2<>"", $C2>=TODAY(), $C2<=TODAY()+30). This captures dates from today through 30 days from now. Adjust the number to change the window.
Can I highlight based on a date in another sheet?
Yes, but the formula must reference the other sheet directly, like =AND(Sheet2!$A2<>"", Sheet2!$A2<TODAY()). However, cross-sheet references in conditional formatting can be tricky; it's often easier to use a helper column on the same sheet that pulls the date with a formula like =Sheet2!A2.
What if my date column has some blank cells?
Blank cells are treated as 0 (January 0, 1900), so they will be less than today. The <>"" check in the formula prevents them from being highlighted. If you want to highlight blank cells too, remove that condition.
How do I remove or edit the rule later?
Go to Home > Conditional Formatting > Manage Rules. Select the rule from the list, then click Edit Rule or Delete Rule. You can also change the range it applies to in the "Applies to" box.
Does the highlighting update automatically each day?
Yes, as long as the workbook is open. Excel recalculates TODAY() when the sheet changes or when the workbook is opened. If the workbook stays open past midnight, the rule updates on the next recalculation (e.g., when you edit a cell or press F9).
Related
Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.