Gantt Chart

[Harvey12]

Hi All,

Can anyone help me build a Gantt Chart from scratch?

I have attempted previous posts to adapt mine, but I personally believe it will be easier to start from scratch?

Does someone perhaps have a project timeline Gantt chart template?

Many thanks
Harvey

 

[oldbrewer]

You will only be able to get a series of horizontal bars for each task. If you need linkages between tasks you need microsoft project manager or a clone - have you looked at shareware, or pay by month project manager ?

 

[Harvey12]

Is there really no way of creating an efficient gantt chart in excel because I have heard of it being done.

 

[oldbrewer]

I personally have used microsoft project and do not believe excel will give other than horizontal bars representing task start and end dates.

 

[Perpa]

Is there really no way of creating an efficient gantt chart in excel because I have heard of it being done.

Harvey12,

If you want a true interactive Gaant Chart, then oldbrewer is right, you need to have a look at a real project manager as described. I have done a few small tests to see what Excel could do, one was for a theatre to visually see how their stages were scheduled.
The other was for someone who wanted a visual of each employee hours scheduled for the day. I am attaching the latter for you to look at. I'm including my step by step procedure and a copy of the starting data (table) and the finished product.

Excel 2007
	A	B	C	D
1	People	Start Time	Duration hh:mm	End Time
2	Bethany	7:00 AM	5:00	12:00 PM
3	Steve	5:00 AM	1:30	6:30 AM
4	Sam	12:00 PM	1:30	1:30 PM
5	Carol	2:30 PM	1:45	4:15 PM
6	Hercules	4:30 PM	4:30	9:00 PM
7	Odell
8	Marcus
9	Betty
10	Mary
11	George
12	Mikey
13	Perpa
14	Fernando
15
Sheet1

After you have entered the data in the 4 columns, select the 'Insert' tab, then in the 'Charts' area select 'Bar Chart'
Then select a 'Stacked Bar Chart', probably the second chart type available. An empty chart should be placed on your worksheet.

Click anywhere on the chart area, from the drop down click on 'Select Data'

On the left hand side, select 'Add', then 'Series Name' select cell B1, select 'Series Values' select the range B2:B14 (or whatever your last row is if not 14)
On the left hand side select 'Add', then 'Series Name' select cell C1, select 'Series Values' select the range C2:C14 (or whatever your last row is if not 14)

On the right hand side select 'Edit', 'Axis Lable values' then select the range A2:A14 ( or whatever your last row is if not 14)
The press 'OK'

You should be looking at a stacked bar graph showing the 'Start Time' in one color and the'Duration' in another.
Click on one of the left most colored 'Start Time' bars, all 'Start Time' bars will be selected. Select 'Format Data Series'
Select 'Fill', then 'No Fill', then 'Close'. Your chart should just show the 'Duration' bars located in the time slots you wanted.

To adjust the x-axis times (across the bottom)and get an even 12 hour distribution:
Right click any of those times, all the times should be selected and a Dialog box should pop up…select 'Format Axis'.
Select 'Number' then 'Custom', then in the box enter this format : '[$-409]hh AM/PM;@' then select 'Add'
Then, still in the 'Format Axis' dialog box, select 'Axis Options> Major Unit>Fixed> enter 0.083333', then 'Close'

Note: It's a good idea to save your chart along the way, sometimes it is easier to start again than to 'undo' something you didn't want to happen. IOW, it's not always apparent by the Excel description what you will end up with.

I'll get back to you with what the finished product looked like, having some issues with the chart image.
Perpa

 

[Perpa]

Harvey12,

If you want a true interactive Gaant Chart, then oldbrewer is right, you need to have a look at a real project manager as described. I have done a few small tests to see what Excel could do, one was for a theatre to visually see how their stages were scheduled.
The other was for someone who wanted a visual of each employee hours scheduled for the day. I am attaching the latter for you to look at. I'm including my step by step procedure and a copy of the starting data (table) and the finished product.

Excel 2007
	A	B	C	D
1	People	Start Time	Duration hh:mm	End Time
2	Bethany	7:00 AM	5:00	12:00 PM
3	Steve	5:00 AM	1:30	6:30 AM
4	Sam	12:00 PM	1:30	1:30 PM
5	Carol	2:30 PM	1:45	4:15 PM
6	Hercules	4:30 PM	4:30	9:00 PM
7	Odell
8	Marcus
9	Betty
10	Mary
11	George
12	Mikey
13	Perpa
14	Fernando
15
Sheet1

After you have entered the data in the 4 columns, select the 'Insert' tab, then in the 'Charts' area select 'Bar Chart'
Then select a 'Stacked Bar Chart', probably the second chart type available. An empty chart should be placed on your worksheet.

Click anywhere on the chart area, from the drop down click on 'Select Data'

On the left hand side, select 'Add', then 'Series Name' select cell B1, select 'Series Values' select the range B2:B14 (or whatever your last row is if not 14)
On the left hand side select 'Add', then 'Series Name' select cell C1, select 'Series Values' select the range C2:C14 (or whatever your last row is if not 14)

On the right hand side select 'Edit', 'Axis Lable values' then select the range A2:A14 ( or whatever your last row is if not 14)
The press 'OK'

You should be looking at a stacked bar graph showing the 'Start Time' in one color and the'Duration' in another.
Click on one of the left most colored 'Start Time' bars, all 'Start Time' bars will be selected. Select 'Format Data Series'
Select 'Fill', then 'No Fill', then 'Close'. Your chart should just show the 'Duration' bars located in the time slots you wanted.

To adjust the x-axis times (across the bottom)and get an even 12 hour distribution:
Right click any of those times, all the times should be selected and a Dialog box should pop up…select 'Format Axis'.
Select 'Number' then 'Custom', then in the box enter this format : '[$-409]hh AM/PM;@' then select 'Add'
Then, still in the 'Format Axis' dialog box, select 'Axis Options> Major Unit>Fixed> enter 0.083333', then 'Close'

Note: It's a good idea to save your chart along the way, sometimes it is easier to start again than to 'undo' something you didn't want to happen. IOW, it's not always apparent by the Excel description what you will end up with.

I'll get back to you with what the finished product looked like, having some issues with the chart image.
Perpa

Here is the link to the finished chart...

 

[Obied70]

This worked great for me (I tried it as a test even though I have a program that will do Gantt charts). Essentially create a stacked bar chart flowing horizontally and then "unfill color" for the part of the bar you dont want to see. Voila, gantt chart. Well done Perpa.

 

[Perpa]

This worked great for me (I tried it as a test even though I have a program that will do Gantt charts). Essentially create a stacked bar chart flowing horizontally and then "unfill color" for the part of the bar you dont want to see. Voila, gantt chart. Well done Perpa.

Harvey,
Glad that worked. I was going to add if you still had questions... that John Walkenbach in his book "Excel 2003 Formulas" discusses Gaant Charts in Chapter 17...There was also a CD included with downloadable files...Gaant Charts was one of the files.
Again, glad that earlier stuff worked for you!
Perpa

 

[Michael M]

Also, have a look at Jon Peltiers site.
There is a Gantt Chart step by step tutorial there....with pictures !!!


Advanced Gantt Charts in Microsoft Excel

 

[Harvey12]

Hi all,

Thank you so much for your responses! It means a lot!

Perpa, many thanks for such a detailed response it's very much appreciated.

Many thanks
Harvey