Formatting Issues

How to Highlight Overdue Tasks and Track Editors in Excel

Maira Mehtab Sep 22, 2026 869 views

Question details

The user needs to highlight overdue tasks based on 'Yes', 'No', or blank statuses, and identify which user updated specific cells in the worksheet.

Product: Microsoft Excel
Device & OS: not provided
Scenario: Managing task deadlines and tracking status updates in a shared spreadsheet.
Observed behavior: Needs conditional formatting rules to visually flag overdue tasks and a reliable method to track cell edit history.

Before you start

Ensure your deadline dates are formatted as valid dates in Excel and that you have appropriate permissions if checking version history in a shared workbook.

Solution 1Recommended

Use Conditional Formatting to Highlight Overdue Tasks

Apply formula-based conditional formatting rules to automatically color code tasks based on their deadline and current status.

Select the target range

Highlight the range of cells containing your task statuses (e.g., column G) that you want to format.

Open Conditional Formatting

Go to the 'Home' tab on the Excel ribbon, click on 'Conditional Formatting', and select 'New Rule'.

Choose formula option

Select 'Use a formula to determine which cells to format' from the list of rule types.

Enter the formatting formula

Input the formula =AND($F3<=TODAY(),G3="N") to highlight overdue tasks marked 'No', adjusting the cell references to match your worksheet layout.

Set the format

Click the 'Format' button, choose your desired fill color (such as red for overdue), click 'OK', and then 'OK' again to apply the rule. You can repeat this process using =AND($F3<=TODAY(),G3="") for blank status cells.

Reference Locking: Make sure to lock the column reference for the date (e.g., $F3) using the dollar sign so the rule evaluates correctly across multiple rows.

Solution 2

Track Cell Editors Using Version History

Standard Excel formulas cannot record who edited a cell, so you must use built-in auditing tools for shared workbooks.

Manage Tasks with WPS Office

Highlight Overdue Tasks Easily with WPS Spreadsheet

WPS Spreadsheet provides powerful and intuitive conditional formatting features, allowing you to seamlessly manage deadlines and track statuses just like Microsoft Excel, but completely free.

1. Open your task tracker: Launch WPS Spreadsheet and open the document containing your tasks and deadlines.
2. Highlight target cells: Select the status cells you wish to format based on the deadline.
3. Create a new rule: Navigate to the 'Home' tab, click 'Conditional Formatting', and choose 'New Rule'.
4. Input the condition: Select the formula option and type in your condition, such as =AND($F3<=TODAY(),G3="N").
5. Apply formatting: Choose an eye-catching fill color for overdue tasks and click 'OK' to save.

Fully compatible with Microsoft Excel (.xlsx) formatsSupports advanced formula-based conditional formattingFree, lightweight, and fast performanceEasy-to-use interface for managing complex task lists

microsoft office alternative - wps office

Frequently Asked Questions

Can I use a formula to automatically insert the user's name when they update a cell?

No, standard Excel formulas cannot capture or record the username of the person editing a cell. You must use VBA macros, Office Scripts, or the built-in Version History feature in Microsoft 365 to track user edits.

Why is my conditional formatting highlighting the wrong rows?

This usually happens due to incorrect absolute or relative cell references in your formula. Ensure your formula locks the column for the date evaluation (e.g., $F3) but allows the row number to adjust relative to the selected range without a dollar sign (e.g., $F3 instead of $F$3).

How do I highlight completed tasks in green?

You can create a new conditional formatting rule for completed tasks. Select your status column, go to Conditional Formatting > New Rule > Use a formula, enter =G3="Y" (assuming column G holds the status and Y means Yes), click 'Format', and choose a green fill color.

Document & File Issues