What is a Gantt chart?

A Gantt chart is a tool to plan and track projects. It shows tasks as bars on a timeline, with each bar representing when a task starts, how long it will take, and when it will end.

This helps teams see the project steps clearly and know which tasks depend on others. A Gantt chart is useful for staying on schedule and managing team work easily.

Creating a Gantt chart in Excel doesn’t need complex formulas, but building the chart does involve several helpful tips and tricks.

Your data

To create a Gantt chart, you must have a table with:

1. Task names
2. Start dates
3. Number of days for each task
4. Calculated End date
5. Gap in Days

• How to Display Days in Letters in your dates

Step 1: Return the End Date (based on working days only)

In Excel, 1 represents one day. A common mistake is adding the start date and the number of days directly, which incorrectly includes weekends. To avoid adding the weekends, you have to use the WORKDAY function, which adds working days to a date.

Step 2: Elapsed Time (including weekends)

When creating a Gantt chart, you'll need both the end date and the total duration (including weekends) from the start to the end of each task. This information will give the lenght of the bar in the chart. The formula is very simple.

And that's all for the calculation! 😍😉😃 Now, we only focus on the chart

Step 3: Choice of the chart

1. Go to the Insert tab
2. Select the Bar Chart menu
3. Select the Stacked Bar chart

But, its possible that Excel generate this type of chart. It's not the one we need to build a Gantt Chart

To change the chart type

1. Select the chart
2. Go to Change Chart Type in the menu Chart Design
3. Choose the Bar section.
4. Select Stacked Bar.
5. Pick the chart with the Task label on the axis.

Step 4: Adjust the Data Series

Now, we will focus on the Chart data series. There are many actions to do. At this step, the Data Series doesn't present the Starting Date.

1. Click on the Add button
2. Select the Serie name with the cell B1
3. Choose the range of cells B2:B14 for the series.

Now, you must apply the following rules

1. The Start Date series must be in first position. Reorder the series with the navigation arrows
2. Keep only 2 series, the Start Date and the Gap. Remove or unchecked the other Series
3. Control that Series for the Axis is the column A only

At this step, your chart look like this.

Step 5: Hide the first bar

The first trick is to hide the first bar.

1. Select your Start Date Series. Double-clic on the series to open the Pane Chart Option
2. Go to the Format option
3. Select the option No Fill

Step 6: 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 7: Change the setting of the horizontal axis

At this step, 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 Horizontal Axis pane by double-clicking on the axis or by selecting this option in the dropdown chart menu

1. Select the Horizontal Axis
2. And the Format option
3. The minimum date value = The number in General format
4. The major unit = 7 (number of days in a week)
5. Label position = High
6. Format number = Date

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

Step 8: Format the bars

To enhance your visual, we will change the gap between the bars and apply a gradient color into the bars

You can also add Data Label into your bars

And that's it! Your Gantt Chart is done 😀👍

Related Articles

• Population Pyramid with Excel
• Sunburst Chart with Excel
• Radar chart in Excel
• Bollinger Bands with Excel
• Waterfall chart in Excel