How to add conditional formatting to Gantt Chart in Smartsheet?

Pratik Shirke

Hello All,

I am preparing a project gantt chart using Smartheet. I have few questions:

1. Can we set up a condition to highlight the gantt chart bar in case there is a delay in the completion of a task? If yes, how??
2. Can we change colour or highlight any text through conditional formatting in case there is any changes in the dates? The change should be highlighted both in the gantt chart and the grid as well.
3. How can we let the end date of the project to be constant, and set a conditional formatting to highlight the delay in the end date or the finishing date of the project in Smartsheet???
4. I tried very hard, but the problem is it gives limited options for conditional formatting, and I want to know how can we set new rules, the way we do it in excel.

I would really appreciate quick responses as it is really getting on my head everyday..!!!

Ben Goldblatt

Hi @Pratik Shirke,

You can definitely use Conditional Formatting to format cell background colors, cell values, and task bars in a Gantt Chart or Calendar. That being said, you may need to insert extra columns and use a combination of formulas and conditional formatting to get your desired results. I highly recommend taking a look at the Project Management Office template set in the Solution Center, as this includes a "Project Plan" sheet that uses Target Start and End dates along with Actual Start and End date columns.

There's a column in this sheet named "Schedule Delta (Working Days), which uses a formula to populate a 1, 0, or -1 based on the actual finish dates listed in the sheet. If a task finishes on the target end date, you'll see a "0", if the actual end date is later than the target end date, you'll see a "1", and a "-1" will be displayed if a task is completed earlier than expected.

With the date variance numbers in place, conditional formatting rules could then be applied using the numbers as the conditions. If I want to format an entire row to have the font color change to a red color and task bars change to red if a task finishes later than the target end date, I could create a rule that reads If Schedule Delta (Working Days) is 1 then apply this format to the entire row. When clicking "this format" to open the formatting palette, I can choose my desired font and task bar colors.

The Highlight Changes feature can be used to highlight cells when changes are made and this can come in handy if the goal is simply to identify which cells have been recently changed. The options for this are limited as this isn't meant to take the place of conditional formatting. The Highlight Changes feature can either be enabled or disabled, you get to set a cell background color for changed cells, and you can select a time interval for which changed cells are highlighted.

To apply formatting based on specific criteria you'll want to use Conditional Formatting, but there isn't currently a way to use "changes to any value" as the condition for your rule. Date-based options in conditional formatting allow for the use of operators such as "is equal to", "is greater than", and "is between".

Check out the Conditional Formatting Help article for more information on setting up formatting rules in your sheets.

I hope this helps!

Thanks,

Ben