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

<tbody>
</tbody>

 

[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.

	A	B	C	D	E	F	G	H
1	Task	Responsible	Start	End	01-Nov	02-Nov	03-Nov	04-Nov
2	Job 1	Alex	01-Nov	05-Nov	Alex	Alex	Alex	Alex
3	Job 2	Chris	02-Nov	04-Nov		Chris	Chris	Chris
4	Job 3	Pat	03-Nov	09-Nov			Pat	Pat
5	Job 4	Chris	03-Nov	07-Nov			Chris	Chris

<tbody>
</tbody>

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.