Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community

conditional formatting for gantt chart

Copper Contributor

Hi, 

 

I use one of the gantt chart template provided on microsoft site to create my first gantt chart. It works really well but I have some trouble with the conditional formatting. I would like all task that are completed (100%) to be green, those partially completed to be 2 colors (green and grey) and those not started (0%) to be blue. The green was easy but everything that are partially or not completed is blue. Can someone help me to fixed this? 

 

Thank you, 

 

Mégan

5 Replies

@Megan365 

You can use conditional formatting to create a Gantt chart in Excel by following these steps:

  • Select the cells that contain the start dates and end dates of your tasks.
  • Go to Home > Conditional Formatting > New Rule.
  • Choose “Use a formula to determine which cells to format”.
  • Enter this formula: =AND($B2<=TODAY(),$C2>=TODAY())
  • Click Format and choose a green fill color. Click OK.
  • Repeat steps 2 to 5 with this formula: =$B2>TODAY() and choose a blue fill color.
  • Repeat steps 2 to 5 with this formula: =$C2<TODAY() and choose a gray fill color.

This will create a Gantt chart that shows the progress of your tasks with different colors. You can adjust the formulas and colors according to your preferences.

 

I hope this helps. 

@NikolinoDE 

 

Hi Nikolino,

 

Thank you verify much for your help. Just to make sure, do I have to delete any previous conditional formatting? And if I apply your recommendation will it change the cell progress or data bars to different color?

 

Thank you again, 

 

Mégan

It is always good to clear the previous conditional formatting so you can avoid any mistakes.

If you take the suggested approach, you should get the result you want...that's the theory :))

I wish you lots of fun and success with Excel.
Thank you very much Nikolino! :)
I'm glad I could help you a bit with your project.

I wish you much success and fun with Excel.