How to Make Gantt Charts in Excel

Every Excel user is familiar with charts and how to generate them easily using templates. However, the Gantt chart is one graph type that many people still find confusing because there is no built-in template for Gantt Chart. So, in this blog, we’ll comprehensively cover all aspects of the Gantt Chart.

What is Gantt Chart?

An American mechanical engineer invented this chart back in 1910. A Gantt Chart is bar chart that is used for illustrating tasks that are to be completed over time. It also highlights connections across project activities, enabling you to keep track of how tasks compare to deadlines or established benchmarks.

Steps to Make Gantt Chart

As we know, there is no built-in template in Excel to make a Gantt Chart. But we can create it by using the bar graph option and can change its formatting according to our need.

To create a Gantt Chart, please follow these steps. We will be using Office 365 for this demonstration, but feel free to use any version you are comfortable with.

1. Create a Project Tabel

To create a Gantt Chart the first step is to set up a project table. To begin, enter data from your project into a spreadsheet as follows:

1. In the first column (column A), enter each project task in a separate row.
2. Add start and end dates for each task in the subsequent columns (column B and column C).
3. In column D, calculate the project duration using a formula, or you can also do it manually.

Duration = End Date – Start Date

4. Optionally you can add the last column (Column E) represents the progress percentage of work.
5. The values in Column F represent the number of days completed for each respective task.

Days Completed = Progress × Duration

2. Create an Excel Bar Chart

After setting our project table we can use this to make a stacked bar chart.

1. The first step is to select the Start Date ranging from B1 to B11.
2. Go into the insert tab then select stacked bar chart from the chart group.

3. Once completed the bar chart should look like this on your worksheet.

4. You can also change the chart design like this.

3. Add Duration Data

Now we’ll add Duration Data to the chart by following these steps:

1. Right-click on the chart then choose Select Data.

2. A new window Select Data Source will appear then click on Add button to select Duration data.

3. The Edit Series window will open, you proceed with the following actions:

To set the series name, enter “Duration,” or click a column header in your spreadsheet while the mouse cursor is in the Series name field to automatically add it as the series name.

To input the series values, click the series value field, then drag the mouse to select the desired range from the relevant cells in the spreadsheet and click OK.

4. You will be redirected back to the Select Data Source box, where you can see that under the Legend Entries (Series) section now includes both Start Date and Duration Data. To add Duration Data to the chart, only click OK.

Now the bar chart will look like this:

4. Add Task Descriptions

Let’s replace the days on the chart’s left side with the list of tasks along the y-axis.

1. Open the Data Source window once more by following the same steps as before: right-click on the chart and choose Select Data.
2. Make sure the Start Date is chosen in the left pane, and then, in the right pane, click the Edit button under Horizontal (Category) Axis Labels in the right pane.

3. In the Axis Label window, select the axis label range from your Tasks, but make sure not to include the column name

4.  Click OK, and the chart will appear as shown below:

In the above chart ,you can notice that the tasks are in reverse order compared to the tasks in the project table.
To overcome this right-click on the tasks, choose Format Axis, and in the Format Axis window on the right side of your sheet, check the box Categories in Reverse Order.

5. Convert the bar graph into an Excel Gantt chart

To convert the stacked bar graph into a Gantt chart, we need to apply the proper formatting. To achieve this, make the blue bars transparent or remove them by following these steps.

• Click on any blue bar and others will be selected automatically.
• Then right-click and choose Format Data Series.
• In the series option select Fill and Line.
• From the Fill option select No Fill.
• And from the Border option select No Line.

Our Gantt Chart is completed but it looks like an ordinary chart at this point.

6. Level up your Excel Gantt chart with a captivating design

To improve the design of Gantt Chart we can add more features to it.

1. Remove the empty space on the left side of the Gantt chart

• Begin by right-clicking on the first Start Date in your data table, and then select Format Cell followed by General. Write down the number that appears, which is the numeric representation of the date.
• We will do the same for the End Date and note down the numerical number of Date.

• Right-click on the date bar on the top of the chart then choose Format Axis and input the Start Date number in the Minimum section and End Date number in the Maximum section.

2. Remove extra white spaces between the bars

We can do this by right-clicking the orange bars then select Format Data series.

• Keep the Series Overlap 100%.
• Change the Gap width to 0%.

3. Now add the Proogress Bar

• Click on the hidden series.
• Select Error Bars followed by More Option.

• From the Format Error Bar Pannel select Plus for the direction.
• No Cap for End Style
• For the Error Amount select Custom then Specified Value.
• After that, a new window named Custom Error Bars will appear. Enter the range of the Days Completed column from the Data Table and click OK.

4. The current condition of our Gantt chart is this:

5. The progress lines are not clear, let’s modify them to make them clearer. We can do this by changing the width until it covers the bars.

• From the Format Error Bars Select Fill and Line.
• Then chose Solid Fill and increase width.

6. Let’s also add data labels to show the percentage completion.

• To do this click the plus icon and then Data Labels followed by More Options.

• Untick the Value and select Value From Cells and enter range.

7. Final Product

Here is the final Gantt chart for our data science project. This chart helps us to visually see how much progress has been made for each task and how much time is left to complete the project.

We can improve the Gantt Chart to increase its effectiveness by changing the font size, assigning separate colors to jobs, adding clear labeling, including visual cues, and employing conditional formatting depending on predetermined criteria.