Gantt – color change

[sammipd]

I’m looking for a way to easily see the task overlap per person in a gantt chart. How can I change the colors in the bar chart to match the color of the staff person assigned the task? Or is there a better way to do this? Thanks!

Col A = Staff
Sam - green
Steve - yellow
Jon - blue
[TABLE="width: 351"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[thisoldman]

I'm not convinced that bar charts are the best method for Gantt charts. I prefer in-cell charts using conditional formatting. See https://www.microsofttraining.net/b/how-to-build-an-automatic-gantt-chart-in-excel/

Here's a variation.

[TABLE="class: grid"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]Task[/TD]
[TD]Responsible[/TD]
[TD]Start[/TD]
[TD]End[/TD]
[TD="align: right"]01-Nov[/TD]
[TD="align: right"]02-Nov[/TD]
[TD="align: right"]03-Nov[/TD]
[TD="align: right"]04-Nov[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]Job 1[/TD]
[TD]Alex[/TD]
[TD="align: right"]01-Nov[/TD]
[TD="align: right"]05-Nov[/TD]
[TD]Alex[/TD]
[TD]Alex[/TD]
[TD]Alex[/TD]
[TD]Alex[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD]Job 2[/TD]
[TD]Chris[/TD]
[TD="align: right"]02-Nov[/TD]
[TD="align: right"]04-Nov[/TD]
[TD][/TD]
[TD]Chris[/TD]
[TD]Chris[/TD]
[TD]Chris[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD]Job 3[/TD]
[TD]Pat[/TD]
[TD="align: right"]03-Nov[/TD]
[TD="align: right"]09-Nov[/TD]
[TD][/TD]
[TD][/TD]
[TD]Pat[/TD]
[TD]Pat[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD]Job 4[/TD]
[TD]Chris[/TD]
[TD="align: right"]03-Nov[/TD]
[TD="align: right"]07-Nov[/TD]
[TD][/TD]
[TD][/TD]
[TD]Chris[/TD]
[TD]Chris[/TD]
[/TR]
</tbody>[/TABLE]

Data entered in A2:D5. Formula in E2, copied down and to the right:
=REPT($B2, AND(E$1>=$C$2:$C$5, E$1<=$D$2:$D$5))

Use conditional formatting in columns E and to the right, assigning a different fill color for each name.

Use custom number formatting to hide the text in the Gantt cells – ";;;" (three semicolons, no quotes, no spaces):

A little custom cell formatting – borders and alignment:

 

[thisoldman]

Here's the link to the example file: https://www.dropbox.com/s/si539ctmuhkntod/gantt_for_sammipd.xlsx?dl=0

 

[sammipd]

thissoldman - you are GOOD! A simple and beautiful solution, thank you!
I'm gonna pitch all my messy bar charts now.

 

[thisoldman]

Thanks for the kind words. I'm glad to have helped.