Gantt Chart in Excel

What Is A Gantt Chart In Excel?

Gantt chart is a project management tool that shows a graphical representation of a project’s schedule across time. Gantt charts are used to showcase the time (from the beginning of the project to the end) along with the sequence and dependencies between project elements.

Here, each activity is represented by a bar, and its length showcases the length of that particular project task. Therefore, it helps us understand the project’s progress from the initial to the completion phase. In addition, Gantt Chart helps the project managers, stakeholders, and team members track the project roadmap, milestones achieved, work dependencies, and risks.

For example, the table below lists the course planner of teachers in column A and the number of days required to teach each chapter in columns, B and C. Now, we need to use the following steps to create Gantt chart in excel.

The steps used to create Gantt chart in Excel are:

Step 1: To begin with, we need to calculate the duration of each chapter using the formula End Date – Start Date.

So, select the start date (column B) in the project task table.

Step 2: Next, go to the Insert tab.

Step 3: Click on the drop-down list of Insert Column or Bar Chart from the Charts group.

Step 4: In this example, let us select 2-D Bar from the available types.

In some time, we can see 2-D stacked bar in our worksheet.

Step 5: Then, right-click on the chart and choose Select Data.

Step 6: First, the Select Data Source window pops up. Click on the Add button.

Step 7: Then, the Edit Series window appears.

Here, choose the Series Name: and Series Values: and click OK.

Step 8: Next, the Select Data Source window pops up. Select the Start Date and then click on the Edit button.

Step 9: Finally, the Axis Labels window pops up. Enter the values in the Axis label range: dialog box.

Step 10: Click OK twice to obtain the chart.

Step 11: Now, right-click on the blue bars and choose Format Data Series.

Step 12: The Format Data Series tab appears on the right side of the Excel sheet.

Click on the No Fill and No Line options.

As soon as we select the formatting options, we can see a Gantt chart in Excel.

Step 13: Now, to add the chart title, select the Chart Title option and enter the chart title as ‘Course Planner’.

We will obtain our Gantt chart as shown in the image below.

Thus, we can insert Gantt chart in excel using inbuilt chart types and formatting options.

How To Create/Make A Gantt Chart In Excel?

Excel offers various kinds of built-in charts and graphs to represent the data visually. Unfortunately, it does not have a built-in feature to create Gantt charts.

However, with the help of bar charts and simple formatting, we can create a Gantt chart in Excel.

Let us learn how to make a Gantt chart in Excel with an example.

Consider the table below listing the tasks and their respective start and end dates in columns A, B, and C. So, we need to use the following steps to create a Gantt chart in excel.

In our example, the table has the required details (tasks, start and end dates of the tasks) to create a Gantt chart.

So, we can use the following steps to create a Gantt chart in excel.

Step 1: First, we need to calculate the duration of each task.

So, in our example, we should create a new column called Duration.

Next, apply the formula End Date (C2) – Start Date (B2) in column D to obtain the duration of each task.

Step 2: Then, we need to create a stacked bar. So, select the start date (column B) in the project task table.

Step 3: Next, go to the Insert tab.

Step 4: And then, click on the drop-down list of Insert Column or Bar Chart from the Charts group.

Step 5: Here, we can select any of the available chart types. For our table, let us choose 2-D Bar.

As soon as we select, we can see 2-D stacked bar in our worksheet.

Step 6: Now, we need to add the duration column to our 2-D stacked chart.

So, right-click on the chart and choose Select Data.

Step 7: First, the Select Data Source window pops up. Click on the Add button.

Step 8: Then, the Edit Series window appears.

Choose cell D1 in Series Name: dialog box and select duration values, i.e., the cell range D2:D5 in Series Values: dialog box. Click OK.

Step 9: The Select Data Source window pops up. Here, we can see that the Start Date and Duration options are selected under Legend Entry Series category.

Step 10: But, we can see only serial numbers under Horizontal (Category) Axis Labels category.  Therefore, we must add Task names here.

So, select the Start Date and then click on the Edit button.

Step 11: After we click Edit, the Axis Labels window pops up.

Step 12: Now, choose task names, i.e., the cell range A2:A5 in the Axis label range: dialog box.

Step 13: Click OK. Now, we can see the four tasks under Horizontal (Category) Axis Labels in the Select Data Source window.

Step 14: Click OK twice to obtain the chart as shown below.

Clearly, we can see that the task names are displayed on the left side, and dates are mentioned at the bottom.

Step 15: Now, we can transform our stacked bar chart into a Gantt cart with simple formatting options.

Right-click on the blue bars and choose Format Data Series.

Or, we can also use the shortcut keys Ctrl + 1.

Step 16: The Format Data Series tab appears on the right side of the Excel sheet.

Click on the Fill icon and choose the No Fill option from the Fill category.

Step 17: The blue bars turn colorless with a visible border.

Now, click on the Border category and select No Line.

Step 18: Finally, click on the X option to close the Format Data Series.

We have now created a Gantt chart in Excel.

Step 19: Next, we need to add the chart title.

So, click on the chart and select the Chart Elements (+) option to add.

Select the Chart Title option. The text box to type the chart title appears.

Now, enter the chart title as ‘Project Gantt Chart’.

We will obtain our Gantt chart as shown in the image below.

Thus, we can insert Gantt chart in Excel using inbuilt chart types and formatting options.

Examples

Example #1: Create a Gantt Chart for Exam Preparation

Mr. Joe is an engineering student who is preparing for his final examinations. So, he wants to track the status of his preparations in multiple phases.

In the table below, we can clearly see that he has identified the phases and calculated the amount of time required to complete each phase.

In the table,

• Column A displays the phases of effective preparation
• Column B shows the starting date of each phase.
• Column C indicates the number of days needed to complete each phase.

Let us learn how to insert Gantt chart in Excel using the following steps.

Step 1: First, select the start date range, i.e., B1:B7.

Step 2: Next, go to the Insert tab.

Step 3: Then, click on the drop-down list of Insert Column or Bar Chart from the Charts group.

Step 4: From the list, we can select any of the available chart types. For our table, let us choose 2-D Bar.

We can see a 2-D stacked bar in our worksheet.

Step 5: Then, right-click on the chart and choose Select Data.

Step 6: First, the Select Data Source window pops up. Click the Add button to add Days to the bar chart.

Step 7: Then, the Edit Series window appears. Choose cell C1 in Series Name: dialog box and select duration values, i.e., the cell range C2:C7 in Series Values: dialog box. Click OK.

Step 8: Next, the Select Data Source window pops up. Meanwhile, we can see that the Start Date and Duration options are selected under the Legend Entry Series category.

Step 9: However, we can see only serial numbers under the Horizontal (Category) Axis Labels category. Therefore, we must add Task names here.

So, select the Start Date and then click on the Edit button.

Step 10: After we click Edit, the Axis Labels window pops up

Now, choose task names, i.e., the cell range A2:A7 in the Axis label range: dialog box.

Step 11: Click OK. Now, we can see all six phases under Horizontal (Category) Axis Labels in the Select Data Source window.

Now, we should click on OK twice. We will have the following stacked bar chart.

Step 12: We can now transform our stacked bar chart into a Gantt cart with simple formatting options.

In the above chart, phases are in reverse order (for example, ‘Material Collection’ is at the bottom), and we need to bring this to the top.

Now, right-click on the phase’s name in the chart, and choose Format Axis.

Or, we can also use the shortcut keys Ctrl + 1.

Step 13: We can see that the Format Axis tab appears on the right side of the Excel sheet.

Please Note: In Excel 2010 and other earlier versions, the Format Axis tab appears in a separate window.

In the Format Axis window, choose Category in reverse order under Axis Options.

Now, we can see the dates on the top and the phases arranged from top to bottom.

Please Note: Do not close the format axis window.

Step 14: Select the blue bars from the chart. Click on the Fill icon and choose the No Fill option from the Fill category.

Step 15: The blue bars turn colorless with a visible border.

Now, click on the Border category and select No Line.

Step 16: Next, we need to format the dates.

Select the Axis Options and enter the minimum and maximum values as 44597 and 44626, respectively.

Note: In Excel, dates are stored as serial numbers; minimum number 44597 represents ‘05-02-2022’, which is the starting date, and maximum number 44626 represents ‘06-03-2022’, which is the last date of the project.

Now, we can see the dates within the project timeline in the chart.

Step 17: Select the bars on the chart and choose Pattern Fill. We can select any of the available patterns.

For our example, let us choose the 5^th pattern.

Step 18: Go to the Chart Design tab and click on Add Chart Element. Select the Chart Title option and choose the Above Chart option.

Step 19: To remove gridlines from the chart, simply click on the Gridlines option and choose the Primary Major Horizontal option.

The Gantt chart appears as shown in the image below.

So, now Mr. Joe can track his studies with the help of the Gantt chart.

Thus, we can insert Gantt chart in excel and customize the chart using patterns.

Example #2: Conditional Formatting Gantt Chart

We learned how to make Gantt chart in excel using the stacked bar chart.

Now, let us learn how to insert Gantt chart in Excel using conditional formatting with an example.

In the table,

• Column A shows the start date
• Column B shows the end date

The steps used to insert Gantt chart in Excel using conditional formatting are as follows:

Step 1: To begin with, enter all start and end dates in horizontal format.

Step 2: Next, we should format the dates such that only the days are visible.

In the Format Cells window, enter the date format ‘DD’ in the Type: dialog box.

Step 3: Then, select all the cells from C2:AG7.

Step 4: Now, in cell C2, enter the following formula.

=AND(C$1>=$A2,C$1<=$B2)

This formula evaluates whether the date in cell C1 is within the start date and end date. If it is within this range, it will return TRUE. if it is not, it returns FALSE values.

Step 5: Next, copy the formula to all the other cells.

Step 6: Then, choose Conditional Formatting under the Home tab and click on New Rule.

Step 7: Now, choose Use a formula to determine which cells to format option in the New Formatting Rule window.

Step 8: In the formula bar, enter the following formula.

=AND($A2>=C$1,$B2<=C$1)

Step 9: Click on the Format tab.

Step 10: In some time, the Format Cells window appears. Click on the Fill tab and choose any color. In this example, let us select the blue color.

Step 11: In the same window, go to Font and choose the same color as the fill.

Step 12:  Click OK, and the New Formatting Rule window appears.

Step 13: Again, click OK. We can see that Excel has substituted the word, TRUE with conditional formatting.

Step 14: Now, repeat the same conditional formatting for FALSE values.

Step 15: Apply the fill and font formatting as white.

Note: Repeat the same in the Font tab as well.

Step 16: Click OK in the next two windows.

Finally, we can see the Gantt chart in the table.

Thus, we can use conditional formatting to insert Gantt chart in Excel.

Gantt Chart Template For Microsoft Excel

Since the Gantt chart in Excel is not a built-in feature, we will provide you with the template to track your project. You can download this report and start using it.

How to use it?

• Cell C1, C2, C3: Enter the names, i.e., the names of the project or company
• Cell C4: Here, we have to enter the project starting date in this cell. We have used the TODAY function to get today’s date. Also, we can change the values in all the cells by just changing in one cell.
• Cell A8:A14: Enter the task names.
• Cell B8:A14: Enter the persons’ names for the tasks.
• Cell E2:E14: The start date is dynamic and it takes the reference from cell C4.
• Cell F2:F14: To arrive end date, we need to add the number of days to the starting date.

• Row 5:5: This row contains the week’s starting date.
• Row 6:6: It is the date format that shows only the day.
• Row 7:7: It is also a date format that displays the day’s short form.

Similarly, we have a group of 7 days in rows 5 to 7.

Then, we should apply conditional formatting to the cells. Remember, it is required to enter the project starting date in cell C4 in this template.

Thus, we can create Gantt chart in Excel with conditional formatting.

Important Things To Note

• The Gantt chart, as the name suggests is named after Henry Gantt. He invented this chart in 1910.
• We can insert Gantt chart in Excel using stacked bar chart and format options.
• Also, it helps us to track the status of the project by looking at the duration and current status.
• Gantt chart can also be created using conditional formatting.
• Remember, the Start date is mandatory to create Gantt charts.
• We can change the pattern, colors and customize our charts to make it more appealing.

Frequently Asked Questions 

Download Template

This article must be helpful to understand Gantt Chart in Excel, with its formula and examples. You can download the template here to use it instantly.

Recommended Articles

This has been a guide to Gantt Chart in Excel. Here we discuss how to create Gantt Chart with examples and an downloadable excel template. You can learn more from the following articles – 

• Sparklines In Excel
• Tables in Excel
• Ribbon In Excel