Chart & Visualization Issues

How to Add Conditional Formatting for Progress in Excel Gantt Charts

Kushani Nimanthika Oct 19, 2026 869 views

Question details

The user needs to modify an Excel Gantt chart using conditional formatting to automatically display task progress with different colors, indicating if tasks are on track or at risk.

How to Create Conditional Formatting for Progress in an Excel Gantt Chart

Product: Microsoft Excel
Device & OS: not provided
Scenario: Tracking project progress where the user wants to visually differentiate task statuses directly within the Gantt timeline based on task completion percentage and remaining days.
Observed behavior: The goal is to automatically color-code task lines based on multiple criteria: completion percentage, project start and duration dates, and the current date.

Before you start

Ensure your Gantt chart spreadsheet is organized with dedicated columns for Start Date, Duration, and % Complete, and a top timeline row containing consecutive calendar dates.

Solution 1Recommended

Apply Formula-Based Conditional Formatting for Progress Tracking

Use advanced logical formulas in Conditional Formatting to evaluate the timeline against task completion and remaining days to automatically colorize chart bars.

By combining the AND and TODAY functions, you can create dynamic rules that update color coding based on real-time schedule risks. The specific formulas below assume your dates timeline is in row 7 (starting at H7), start dates are in column E, durations in column F, and completion percentages in column D. Adjust these relative and absolute references based on your own sheet's layout.

Select the Gantt chart timeline area

Highlight the entire grid area of your Gantt chart where the task bars are displayed (for example, H9 to AZ100).

Create a new formatting rule

Navigate to the Home tab on the ribbon, click 'Conditional Formatting', and select 'New Rule'. Choose 'Use a formula to determine which cells to format'.

Apply the 'On Track' rule (Green)

To format tasks that are more than 90% complete with 30 days remaining in green, enter this formula: =AND(H$7>=$E9+1,H$7<=$E9+$F9-2,$D9>90%,$E9-30<TODAY()). Click 'Format', choose a green fill color, and click OK.

Apply the 'At Risk' rule (Red)

Create another new rule for the same range. To format tasks that are 20% complete or less with 30 days remaining in red, enter this formula: =AND(H$7>=$E9+1,H$7<=$E9+$F9-2,$D9<=20%,$E9-30<TODAY()). Click 'Format', choose a red fill color, and click OK.

Review and manage rules

Go to 'Conditional Formatting' > 'Manage Rules' to ensure your rules are applied in the correct order and the 'Applies to' range covers your entire chart area.

Apply Formula-Based Conditional Formatting for Progress Tracking

Important Note on Cell References: The placement of the dollar signs ($) is critical. $E9 locks the column but allows the row to change for each task, while H$7 locks the row but allows the column to change for the timeline dates.

Solution 2

Use a Pre-built Excel Gantt Chart Template

Instead of writing complex formulas from scratch, utilize built-in templates that already include conditional formatting for task progress.

Professional Project Management

Create and Manage Dynamic Gantt Charts in WPS Spreadsheet

WPS Spreadsheet provides a highly compatible and intuitive environment for building dynamic Gantt charts. You can seamlessly apply complex conditional formatting rules just like in Excel, or utilize a rich library of pre-made project management templates to track progress effortlessly.

1. Open your project file: Launch WPS Spreadsheet and open your existing project tracker or create a new one using a built-in Gantt chart template.
2. Access Conditional Formatting: Highlight the timeline cells where you want task bars to appear. Navigate to the 'Home' tab and click on 'Conditional Formatting'.
3. Input progress tracking formulas: Select 'New Rule' > 'Use a formula to determine which cells to format', and input your tracking logic (e.g., combining AND and TODAY functions).
4. Set color indicators: Click 'Format' to assign specific fill colors (like green for on-track or red for at-risk) and apply the rule to instantly update your chart.

100% compatible with Microsoft Excel (.xlsx) formats and conditional formatting formulasBuilt-in template library offering professional Gantt chart and timeline layoutsLightning-fast calculation engine handles complex progress-tracking formulas smoothlyFree to download and fully functional across Windows, Mac, and mobile devices

microsoft office alternative - wps office

Frequently Asked Questions

Why are my conditional formatting formulas formatting the wrong cells in the Gantt chart?

This usually happens due to incorrect absolute and relative cell references. Ensure that timeline date references lock the row (e.g., H$7) and task data references lock the column (e.g., $E9). If the dollar signs are misplaced, the formula evaluates the wrong cells.

Can I use conditional formatting to highlight 100% completed tasks in my Gantt chart?

Yes. You can add a new rule that specifically targets tasks where the completion percentage equals 100%. Use a formula like =$D9=100% (assuming D is your completion column) and format those cells with a distinct color, like gray, or a strikethrough effect.

Will my Excel conditional formatting rules for Gantt charts work if I open the file in WPS Office?

Absolutely. WPS Spreadsheet is highly compatible with Excel formulas and formatting features. The conditional formatting logic, including complex AND/TODAY functions, will execute seamlessly when you open your .xlsx file in WPS Office.

Excel & Data Problems