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

Share this:

Like this:

5 Responses to “Gantt Charts in Excel”

Even though I wrote that page and the one on my other web site some years ago (www.duncanwil.co.uk) and when I did so it was something of a pioneering page, I don’t prepare Gantt charts that often at the moment. So, when I needed to prepare a Gantt chart the other day I came here to remind myself of what to do … it’s a breeze! Seriously, read these instructions carefully and you shouldn’t go wrong. the only thing YOU need to do is to sort out your colour schemes and formatting … I am including a picture of my new Gantt chart within this article now …