A Gantt Chart looks like this:

gant_030111

 

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:

gantt_chart

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

5 Responses to “Gantt Charts in Excel”


  1. 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 …

    Duncan

  2. sanu Says:

    Hi Mr.Duncan,

    I just bought your book Excel Project for Accountants (Kindle as well as paper back).Overall the book makes an excellent presentation of most useful functions in excel.

    But I am at a loss regarding the excel files.

    Can u please post as link.

    Rgds,

    Sanu
    sanucwa@gmail.com


    • Thanks and apologies Sanu. I am checking this now but so far they seem to be where they should be. One more check and I will come back to you with any changes.

      Duncan


    • I have sent you an email Sanu explaining how to find the files on my web site: available to everyone who buys this book.

      Thank you for your kind words about my book too!

      Duncan

  3. sanu Says:

    Thanks Mr.Duncan for the post.Really appreciate your immediate feedback.

    Once again my sincere appreciation for the most useful excel book ever meant for accountants/analysts !!!

    May be you can top it up with a financial modelling book using excel.

    Regards,

    Sanu


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: