What is a Gantt chart?
A Gantt chart, helps you to visualize the time needed to realize actions and also the critical step to avoid to start a step before the end of another one.
To create a Gantt chart, you must have a table with:
- Task names
- Start dates
- Effort (number of days needed to carry out each task)
Step 1: Return the End Date (based on working days only)
In Excel, 1 is one day (see this page about the date). And a common mistake is to add the start date with the number of days.
But, this operation is wrong because here you include the weekend.
To avoid adding the weekends, you have to use the WORKDAY function. This function just adds working days to a date.
Step 2: Elapsed Time (including weekends)
When you create a Gantt chart, as well as the end date, you also need the elapsed time (time elapsed from start to finish of a task, including weekends) between the end date and the start date. The formula is quite simple.
And that's all for the calculation! 😍😉😃 Nothing complex at all. Now, it's time to create the chart
Step 3: Choice of the chart
To create a Gantt chart, you have to create a horizontal stacked bar chart
But for the moment, the chart doesn't look like a Gantt chart 🤔
Step 4: Remove series of data
Here, we need to remove the series that are not useful. In fact, the 2 series we must keep are:
- Start date
- Elapsed time (including the weekends)
To remove series from your chart, you have to open the dialogue box "Select Data Source". There are 2 ways to do this:
- Menu Design > Select Data
- Right-click on your chart and select the menu Select Data
In the dialogue box, uncheck the effort and end date.
The chart becomes:
Select a specific chart element
For the next steps, it is important to know how to easily select a chart element like axis, series, title,....
- Select your chart
- Go to the Format tab
- On the left hand-side you have a drop-down list with all the chart elements
- Select the one you want to modify
- Click on the button Format Selection (just under)
Step 5: Change the vertical axis
Now, we must reverse the chart to have task #1 on the top of the chart.
- Double-click on the vertical axis to show the Axis pane
- In the Axis pane, select the option Categories in reverse order
Step 6: Change the start date on the axis
First, we need to know the value in days of the first date of our model. To do that, we just have to change the number format in General.
Then, open the Axis pane by double-clicking on the axis or with the method describe previously and change
- The minimum date value = The number in General format
- The major unit = 7 (number of days in a week)
- Label position = High
- Format number = Date
To improve the result, change the alignment of values by rotating -30 ° relative to their horizontal axis.
The Gantt chart looks better now but we have one more step.
Step 7: Hide the first bar
The last step is to hide the first series (the start date)
- Select the option Format Data Series
- Select the Painting option
- Fill = No Fill
- Border = No Fill
Your chart is now finished!
Improving the design
Use your artistic talent to add some beautiful colours and labels 🎨🧐