Chart & Visualization Issues

How to Highlight Overdue In-Progress Gantt Chart Cells in Red

Muhammad Talha Oct 1, 2026 869 views

Question details

The user needs to configure a Gantt chart so that cells from the task's end date up to today's date turn red if the task is still marked as 'In Progress', and the highlighting should stop once the task is completed.

How to Highlight Overdue In-Progress Gantt Chart Cells in Red

Product: Spreadsheets
Device & OS: not provided
Scenario: Tracking project timelines and visually identifying tasks that are overdue but not yet completed on a spreadsheet Gantt chart.
Observed behavior: The user requires a specific conditional formatting rule to dynamically evaluate the task status, end date, and current date to apply the red highlighting accurately.

Before you start

Ensure your Gantt chart is organized with a dedicated column for Task Status (e.g., 'In Progress', 'Complete'), a column for the End Date, and chronological date headers across the top of your timeline grid.

Solution 1Recommended

Use Conditional Formatting with an AND() Formula

Create a custom conditional formatting rule that uses the AND function to simultaneously check if the task is 'In Progress', if the timeline date is past the end date, and if the timeline date is on or before today.

To achieve this, you need a formula that evaluates three conditions for every cell in your Gantt chart timeline. By carefully placing absolute ($) and relative references, a single formula can format the entire grid accurately.

Select your timeline grid

Click and drag to highlight the entire area of your Gantt chart where the timeline bars appear (for example, select from cell G2 down to Z100).

Open Conditional Formatting

Navigate to the 'Home' tab on the ribbon, click on 'Conditional Formatting', and choose 'New Rule'.

Choose the formula option

In the dialog box, select 'Use a formula to determine which cells to format'.

Enter the custom formula

Input your formula based on your columns. Assuming Column B is Status, Column C is End Date, and Row 1 contains your timeline dates, enter: =AND($B2="In Progress", G$1>$C2, G$1<=TODAY())

Set the cell format to red

Click the 'Format' button, go to the 'Fill' tab, select the color red, and click 'OK' twice to apply the rule to your Gantt chart.

Use Conditional Formatting with an AND() Formula

Understanding Cell References: The dollar signs ($) are crucial. Locking the column for Status and End Date (e.g., $B2, $C2) ensures the formula always looks at those specific columns, while locking the row for the timeline headers (e.g., G$1) ensures the formula always checks the dates at the top.

Create Powerful Gantt Charts Easily in WPS Spreadsheet

WPS Spreadsheet offers an intuitive interface and robust conditional formatting capabilities, making it exceptionally easy to build dynamic Gantt charts and automate your project tracking without complex setups.

1. Open your project file: Launch WPS Spreadsheet and open the workbook containing your project data.
2. Highlight the timeline: Select the range of cells that represent the timeline portion of your Gantt chart.
3. Access Conditional Formatting: Go to the Home tab, click the Conditional Formatting icon, and select 'New Rule'.
4. Apply the rule: Choose the formula option, paste your AND() logic checking the task status and dates, and set the fill color to red.
5. Save and track: Click OK. Your Gantt chart will now dynamically highlight overdue in-progress tasks, updating automatically each day.

100% compatible with Microsoft Excel conditional formatting rules and functionsAdvanced custom formula support for dynamic project timelinesRich library of free project management and Gantt chart templatesLightweight, fast, and completely free to use

microsoft office alternative - wps office

Frequently Asked Questions

Why is my conditional formatting rule highlighting the wrong cells on the timeline?

This usually happens due to incorrect absolute and relative cell references. Ensure you use the dollar sign ($) to lock the columns of your Status and End Date (e.g., $B2) so the check doesn't shift left or right, and lock the row of your timeline dates (e.g., G$1) so it doesn't shift up or down.

How do I make the red highlight disappear when the task is finished?

The formula handles this by checking if the task is 'In Progress'. If your formula contains `$B2="In Progress"`, changing the status cell in column B to 'Complete' or 'Done' will cause the condition to evaluate as FALSE, immediately removing the red highlight.

Can I highlight the entire row of the overdue task instead of just the timeline cells?

Yes. To highlight the whole row, select your entire data table (e.g., A2:Z100) instead of just the timeline grid. Then, simplify your formula to only check the End Date and Status, such as `=AND($B2="In Progress", $C2<TODAY())`, and apply your desired fill color.

Excel & Data Problems