Gantt Charts in Excel

A Gantt Chart looks like this:

 

I made a comment on this page about 30 minutes ago and said I would insert a new Gantt Chart, one that I prepared for a client last week, just to show that I’m still doing it! Here it is:

And lots of people use them and/or need to use them. The question is, how on earth do you prepare them in Excel. After all there isn’t a Gantt chart option in there. It’s true, there is no Gantt chart option in Excel. It is possible to draw a Gantt chart in Excel though because that’s where that chart above came from!

Here are the data on which the above Gantt chart is based:

task	start date	duration	end date
planning meeting	29/12/2010	1	29/12/2010
develop questionnaire	30/12/2010	11	09/01/2011
print and mail questionnaire	13/01/2011	9	21/01/2011
receive responses	16/01/2011	15	30/01/2011
data entry	16/01/2011	18	02/02/2011
data analysis	03/02/2011	4	06/02/2011
write report	09/02/2011	12	20/02/2011
distribute draft report	23/02/2011	1	23/02/2011
solicit comments	24/02/2011	4	27/02/2011
finalise report	02/03/2011	5	06/03/2011
distribute to board	09/03/2011	1	09/03/2011
board meeting	17/03/2011	1	17/03/2011

A series of tasks with a start date, an end date and a duration: typical data for a Gantt chart. Please note, the end date column and data are not needed to draw the Gantt chart, they are there for information and confirmation

Please copy and paste that table into a worksheet with the heading task in cell A5. Now work your way down the following instructions that relate to Excel 2007 (and 2010):

How to Draw a Gantt Chart

1 Enter the data as shown in Table 1. The formula in cell D6 (end date), which was copied to the rows below it, is =B6+C6-1 the end date column is NOT used in the chart but is for information to support it

2 To create a chart select the rangeA6:C17 then click Insert and create a stacked bar chart … use the second subtype, which is labelled Stacked Bar.

3 Notice that Excel incorrectly uses the first two columns as the Category axis labels.

4 Right click the chart and click on Select Data to open the chart wizard. Now, set the chart’s series to the following:

• Series 1: B6:B17
• Series 2: C6:C17
• Category (x) axis labels: A6:A17

Click OK to leave the chart wizard to create an embedded chart.

5 Delete the legend

6 Create or amend the title and add the horizontal axis label … this is no real need for the vertical axis label but feel free to add Task if you wish.

7 Adjust the horizontal axis Minimum and Maximum scale values to correspond to the earliest and latest dates in the data (note that you can enter a date into the Minimum or Maximum edit box). You might also want to change the date format for the axis labels.

8 Right click the vertical axis and select the Number sub menu: select Format Axis dialog box for the vertical axis. In the Axis options select the option labelled Categories in reverse order and also select the option labelled Horizontal axis crosses at maximum category.

9 Right click the first data series and select Format Data Series. In the Fill section, set fill to No fill and Border Colour to no line. This makes the first data series invisible and is the key to this chart.

10 Apply other formatting, as desired.

That’s it! Read this information once, twice, as many times as you need. Then enter your own data, adjust the formulae and change the data series in the chart, as you need.

Duncan Williamson