How to create a Gantt Chart with Excel?

How to create a Gantt Chart with Excel?
Last Updated on 10/11/2023
Reading time: 4 minutes

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 starting a step before the end of another one.

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

=B2+C2

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

=WORKDAY(B2,C2)

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.

=D2-B2

And that's all for the calculation! 😍😉😃

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 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 an 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 🎨🧐

Gantt chart in Excel

Leave a Reply

Your email address will not be published. Required fields are marked *

How to create a Gantt Chart with Excel?

Reading time: 4 minutes
Last Updated on 10/11/2023

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 starting a step before the end of another one.

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

=B2+C2

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

=WORKDAY(B2,C2)

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.

=D2-B2

And that's all for the calculation! 😍😉😃

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 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 an 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 🎨🧐

Gantt chart in Excel

Leave a Reply

Your email address will not be published. Required fields are marked *