Formatting Issues

How to Automatically Highlight Today's Date in Red in Excel

WPS Editor Oct 9, 2026 869 views

Question details

The user wants to automatically format cells containing the current calendar date with a red color to easily identify them.

How to Automatically Highlight Today's Date in Red in Excel

Product: Microsoft Excel
Device & OS: not provided
Scenario: Tracking expiry dates, deadlines, or daily tasks that require immediate attention when they match the current calendar date.
Observed behavior: The user needs the spreadsheet to automatically recalculate every day and apply a red highlight specifically to dates matching today's calendar date.

Before you start

Ensure your expiry dates are formatted as valid dates in Excel (and not as plain text) so the formula can correctly compare them to the system's current calendar date.

Solution 1Recommended

Use a Formula to Highlight Today's Date

This method uses the TODAY() function within conditional formatting to exactly match and highlight the current date dynamically.

By leveraging the TODAY() function within a custom Conditional Formatting rule, Excel will compare your list of dates against the current system date. This formatting updates automatically whenever you open or recalculate the workbook.

Select the target range

Highlight the column or specific cells containing your expiry dates (for example, select cells A2 through A100).

Open Conditional Formatting

Navigate to the "Home" tab on the Excel ribbon, click on the "Conditional Formatting" button, and select "New Rule" from the drop-down menu.

Enter the TODAY() formula

Choose the option "Use a formula to determine which cells to format". In the formula box, type `=A2=TODAY()` (ensure you replace 'A2' with the very first cell of your highlighted range).

Apply red formatting

Click the "Format" button, navigate to the "Fill" or "Font" tab, select a red color to make the dates stand out, and click "OK" twice to apply and save the rule.

Automatic Daily Updates: Excel will automatically recalculate the TODAY() function every time you open the workbook, ensuring the red highlight shifts to the new current day automatically.

Solution 2

Highlight Overdue or Today's Dates Using Built-in Cell Rules

If you want to highlight dates that are today or have already passed (expired), use the standard cell value rules instead of an exact match.

Powerful Excel Alternative

Easily Manage Dates and Conditional Formatting in WPS Spreadsheet

WPS Spreadsheet offers full support for advanced conditional formatting and formulas like TODAY(), allowing you to effortlessly track deadlines. It's free, intuitive, and works seamlessly with your existing spreadsheets.

1. Open your file in WPS: Launch WPS Spreadsheet and open your existing Excel workbook containing the expiry dates.
2. Access Conditional Formatting: Select the dates you want to monitor, navigate to the "Home" tab, click "Conditional Formatting", and select "New Rule".
3. Apply the rule: Choose "Use a formula...", input `=A1=TODAY()`, set your preferred red format, and click "OK" to see immediate results.

Fully compatible with Microsoft Excel (.xlsx) formats and existing conditional formatting rules.Intuitive UI for applying complex date tracking and custom formulas.Lightweight, fast-loading, and completely free to use for your daily tasks.

Frequently Asked Questions

Why is my conditional formatting formula not working correctly?

Ensure that the cell reference in your formula (e.g., A1) matches the top-left cell of the actual range you selected before creating the rule. Also, verify that your data is stored as actual dates, not as text strings.

How can I highlight dates that occur tomorrow?

You can use the exact same conditional formatting method, but modify the formula to add one day. Use `=A1=TODAY()+1` to highlight tomorrow's date.

Can I highlight the entire row instead of just the date cell?

Yes. Select the entire data table (e.g., A2:F100) before creating the rule. When entering the formula, use a dollar sign to lock the column containing the date, like `=$A2=TODAY()`. This applies the red format to the entire row.

Will the red highlight update if I leave the file open for days?

The TODAY() function updates when the workbook recalculates. If you leave the file open continuously overnight, you may need to press F9 to force a manual recalculation so the highlight shifts to the new date.

Document & File Issues