↑ Return to Charts

Gantt chart

Presentation

With Gantt chart, you can easily visualize the planning of your project with the beginning and the end of different tasks.

There are expensive software that allow you to make these chart but with a little ingenuity, you can create sophisticated Gantt charts with Excel.

Your data

 

 

To create a Gantt chart, you have a workbook which shows the name of tasks, starting dates and number of days to realize each task.

 

 

 

 

 

Calculating the number of work days

In Excel, 1 is one day (see this page on dates). A common mistake is to add the start date with the number of days. But, this operation obscures the problem of the weekend.

=B2+C2

Therefore, it is necessary in this case use the WORKDAY function to perform the calculation. In D2 we have the following formula

= WORKDAY(B2,C2)


But, when you create a Gantt chart, you must keep the number of days rather than the end date. You must write in the column E a new formula which returns the interval of days between the new calculated end date and the start date.

= D2-B2

Choice of the chart

Activate the menu Insert> Chart and select the chart type ‘Bar’, then the second sub-type ‘Stacked Bar’.

 

You get the following graph (heck,  it’s ugly 😕 )

Remove unnecessary data

In our graph we have the number of days and the end date in the chart legend. These information are useless. We must remove them to show only the start date and the actual number of days calculated.

Select the chart and select the menu Design > Select Data. The following dialog appears:


 

You could also display this dialog box by doing a right click on your chart and choosing ‘Select data

In this dialog, you choose Series number of days and you click Remove. Repeat this operation for the end date. If ever you make a mistake, don’t worry, just click the Cancel button and start over.

 

 

The chart becomes:

Changing the vertical axis

This is the decisive moment.

We must reverse the chart to have the task #1 on the top. To do this, select the vertical axis (using the dropdown menu in the Layout tab is easier) and then you click Format Selection.

In the Settings box that appears, you select “Axis Options” and you select Categories in reverse order (to return the chart).

Changing the horizontal axis

Return to your data and convert the first date in number format. This figure is the number of days between your first date and January 1900 (See this page about the Date format).

Select the horizontal axis and the sub-menu options see the figure axis as the minimum value (enable the option to enter your own fixed value). The first task is then joined to the vertical axis. And as you turn the main unit 7 to be a gap of one week each time. Finally, set the High Axis labels to display the dates at the bottom of your diagram.

To improve the result, change the alignment of values by rotating -30 ° relative to their horizontal axis.

The Gantt chart is beginning to take shape.

Remove some chart elements

To complete our Gantt chart, we will remove the chart legend. You can either activate the menu of the image below to remove the legend, or select the legend on your chart and press the Delete key.

Then select the first set of data and the formatting options of series, indicate that you do not want a border or color for this data.

 

Your chart is now complete :)

Improving the design

You can easily improve the result by using the formatting options embedded in Excel.

Leave a Reply

%d bloggers like this: