How to Use Excel Conditional Formatting with TODAY and Relative Row References
Question details
The user needs to create conditional formatting rules that evaluate each row independently using the TODAY function and relative row references.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Applying conditional formatting across multiple rows where the formula depends on the current date and specific column values for each individual row.
- Observed behavior
- The user wants to lock the source column while allowing the row reference to change automatically when the formatting rule is applied to a target range.
Ensure your date data is formatted as valid dates, not as text, and select the entire range you want to format before entering your custom formula.
Apply Conditional Formatting Using Mixed References and the TODAY Function
Use a mixed reference to lock the column while keeping the row dynamic, allowing the rule to evaluate each row independently against the current date.
By locking the column with a dollar sign (e.g., $A) but leaving the row number relative (e.g., 1), the software automatically adjusts the row number for each cell in your selected range. Combining this with the TODAY() function is perfect for tracking deadlines, highlighting overdue items, or creating status indicators.
Select the cell range (e.g., $B$1:$B$9) where you want the conditional formatting to appear.
Go to the Home tab on the ribbon, click 'Conditional Formatting', and select 'New Rule'.
Choose 'Use a formula to determine which cells to format' from the rule types list.
Input your formula using a mixed reference. For example, to highlight dates older than 10 days, enter =$A1<=TODAY()-10.
Click 'Format', choose your desired fill color (like orange or green), click 'OK' to confirm the format, and 'OK' again to apply the rule.
Use WPS Spreadsheet for Advanced Conditional Formatting
WPS Spreadsheet provides robust conditional formatting tools that are fully compatible with Excel formulas, including mixed references and the TODAY() function. It is a free, lightweight alternative that makes tracking deadlines and dynamic dates intuitive.
- 1. Open your file in WPS: Launch WPS Spreadsheet and open your existing workbook containing the date data.
- 2. Select your data range: Highlight the cells you want to format based on the dynamic date rule.
- 3. Create a new rule: Navigate to Home > Conditional Formatting > New Rule.
- 4. Enter the formula: Select 'Use a formula...', input your rule like =$A1<=TODAY()-10, set the format color, and click OK.
Frequently Asked Questions
Why is my conditional formatting applying the same rule to every row?
This usually happens if you use absolute references for the row (e.g., $A$1 instead of $A1). Ensure you remove the dollar sign before the row number so the formula can adjust for each row in your selected range.
Can I use the TODAY function to highlight upcoming dates?
Yes, you can create a rule to highlight dates occurring in the future. For example, to highlight dates within the next 7 days, use a formula combining AND with TODAY: =AND($A1>TODAY(), $A1<=TODAY()+7).
Does the TODAY() function update automatically?
Yes, the TODAY() function recalculates automatically every time you open the workbook or whenever the spreadsheet recalculates, ensuring your conditional formatting is always based on the current date.
