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.
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.
Therefore, it is necessary in this case use the WORKDAY function to perform the calculation. In D2 we have the following formula
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.
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 …‘
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.