Gantt Chart

What is a Gantt chart?

A Gantt chart helps you to visualize your project schedule containing different tasks and their deadlines.

Your data

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 the 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.

=B2+C2

To avoid adding the weekends, you have to use the function WORKDAY. This function just adds working days to a date.

=WORKDAY(B2,C2)

Step 2: Elapsed Time (including weekends)

When you create a Gantt chart, aswell 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.

=D2-B2

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

Horizontal stacked bar chart

But for the moment, the chart doesn't looks 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 dialog 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 dialog 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.

  1. Double-click on the vertical axis to show the Axis pane
  2. 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)

  1. Select the option Format Data Series
  2. Select the Painting option
  3. Fill = No Fill
  4. Border = No Fill

Your chart is now finished!

Improving the design

Use your artistic talent to add some beautiful colors and labels 🎨🧐

Permanent link to this article: https://www.excel-exercise.com/gantt-chart/


Leave a Reply

Your email address will not be published.