Creating Gantt Charts in Microsoft Excel – Part 1: The Basics

Here’s a handy trick. If you need to create a simple Gantt chart to illustrate a point, and you don’t have Microsoft Project or similar software, you can create Gantt charts that are attractive and easily updated in Microsoft Excel. For an example, check out
the official website of Carlton Dramatic Society, which I run ran for several years.

Here’s how. The key points are using a Stacked Bar Chart to represent the data, and tricking the chart into showing the correct scale. This tutorial uses Excel 2000.

  1. Start a new workbook in Excel. In a blank sheet, enter your task data in 3 columns; Task Name, Start Date, and Duration in Days. See Table 1 below for an example.
  2. Select all the entered data, and select Insert>Chart. Select the Stacked Bar Chart type, and click Next.
  3. Ensure the Series are selected to come from Columns, then click the Series tab.
  4. There should be 2 data series, with the following attributes;
    Start Date: Name=$B$1, Values=$B$2:$B$4,
    Duration in Days: Name=$C$1, Values=$C$2:$C$4
    The X Axis labels should be: $A$2:$A$4. If this is all OK, click Next.
  5. Now we set up various options. On the Titles tab, you can enter captions for the Title and axes. Switch off the Legend. Click Next.
  6. Select where you want the chart to appear, and click Finish. Your chart should appear. Now for some tweaking.
  7. Click on the vertical axis (X axis in this case). Right-click and select Format Axis. On the Scale tab, tick ‘Categories in reverse order’ and click OK. This makes the Tasks appear in order.
  8. Now here’s a clever bit. Right-click on the horizontal (Y) axis, and select Format Axis. On the Scale tab, in the Minimum box, enter your project start date (it will recognize the format). Try and make it a Monday. Leave all the others Automatic – it should work OK. Click OK.
  9. Double-click on the left-hand part of one of the bars. This will open the Format Data Series dialogue for the ‘Start Date’ series. Give the bar ‘Fill=None’, and a dotted border (or no border). Click OK.
  10. You should now have something that looks like Figure 1 below.

This is a simple Gantt chart, and the beauty is, you can alter your dates and Task names, and see the changes in the chart straight away. When adding tasks, just make sure you change the data series ranges.

Table of example data below.

1 Start Date Duration in Days
2 Task 1 07/01/05 3
3 Task 2 10/01/05 23
4 Task 3 14/01/05 4

Figure 1: Example Gantt Chart