Function Problems

How to Highlight Today and Adjacent Cells with Excel Conditional Formatting

Maira Mehtab Sep 22, 2026 870 views

Question details

The user needs to dynamically highlight the current day of the week and its corresponding adjacent cell containing schedule information using a conditional formatting rule.

Product: Microsoft Excel
Device & OS: not provided
Scenario: Managing a weekly schedule where days of the week (Sunday through Saturday) are listed in one column, and additional details like closing times are listed in the adjacent column.
Observed behavior: The user wants the formatting to apply to both the cell containing the day and the cell next to it based on today's date.

Before you start

Ensure that the days of the week in your spreadsheet are spelled correctly as text (e.g., 'Monday') and exactly match your system's language output for the TEXT function.

Solution 1Recommended

Use a Formula-Based Conditional Formatting Rule

Apply a conditional formatting rule using the TEXT and TODAY functions combined with a mixed reference to highlight the current day and adjacent cells.

To highlight an entire row or multiple cells in a row based on a single cell's value, you must use a mixed cell reference (like $F2). This locks the column so Excel checks the day in column F even when evaluating the rule for column G.

Select the target range

Highlight the entire range you want to format, such as F2:G8. Ensure that the top-left cell (F2) is the active cell during this selection.

Open Conditional Formatting

Navigate to the Home tab on the Excel ribbon, click on 'Conditional Formatting', and select 'New Rule' from the dropdown menu.

Enter the formatting formula

Choose 'Use a formula to determine which cells to format'. In the formula box, enter =F$2=TEXT(TODAY(),"dddd") but correct the locking to =$F2=TEXT(TODAY(),"dddd"). The dollar sign before the F ensures the adjacent column G will also look at column F.

Apply formatting style

Click the 'Format' button, navigate to the 'Fill' tab, choose a highlight color like yellow, and click 'OK' to save and apply the rule.

Important Details on Locking Columns: If you use =F2 without the dollar sign, the conditional formatting will only work when selecting a single column (F2:F8). For multiple columns (F2:G8), =$F2 is required to make the adjacent cells highlight correctly.

Highlight Cells Conditionally in WPS Spreadsheet

WPS Spreadsheet fully supports all advanced Excel conditional formatting rules and dynamic formulas like TODAY(). You can easily set up auto-updating schedules with highlighted rows for the current day in just a few clicks.

1. Open your schedule: Launch WPS Spreadsheet and open the document containing your weekly schedule.
2. Access conditional formatting: Select the data range (e.g., F2:G8). Go to the Home tab, click Conditional Formatting, and choose New Rule.
3. Set the formula: Select 'Use a formula to determine which cells to format', enter =$F2=TEXT(TODAY(),"dddd"), set your desired fill color, and click OK.

100% compatible with Microsoft Excel conditional formatting and formulasLightweight and fast performance for complex data setsFamiliar user interface requiring zero learning curveFree to use for daily professional spreadsheet tasks

microsoft office alternative - wps office

Frequently Asked Questions

Why is the adjacent cell in my schedule not highlighting?

If only the first column is highlighting, you likely missed the dollar sign ($) in your formula. Make sure your formula is =$F2=TEXT(TODAY(),"dddd") to lock the evaluation to column F. Also, ensure you selected both columns (e.g., F2:G8) before creating the rule.

Why doesn't the TODAY() function update automatically on a new day?

The TODAY() function updates whenever the workbook recalculates. If you leave the workbook open overnight, it might not refresh immediately. You can force a recalculation by pressing the F9 key or simply reopening the file.

How do I format the row if my cells contain actual dates instead of text?

If your cells contain real date values (like 10/25/2023) but are formatted to display the day name, the TEXT function won't match properly. In this case, simply use the formula =$F2=TODAY() in your conditional formatting rule.

Excel & Data Problems