Gantt Chart in Excel

What Is A Gantt Chart In Excel?

Gantt chart is one of the project management tools that shows a graphical representation of a project’s schedule across time. Also, 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.

Each activity will be represented by a bar, and each bar length will showcase the length of that project task. Therefore, it helps us understand the project’s progress from the initial phase 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.

 Intro

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.

Gantt chart in excel Intro Example

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

Key Takeaways
  • Gantt chart is not a built-in chart in Excel. So, we need to build a stacked bar chart and then apply formatting to convert it into a Gantt chart in Excel.
  • Gantt chart helps us to track the status of the project by looking at the duration and current status.
  • Duration is an important element to create Gantt charts in excel. The formula used to find duration is =End Date – Start Date
  • The #VALUE! Error occurs when the dates are not in correct format

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.

How To Create

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.

Gantt chart in excel How To Create.1

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

How To Create.2

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.

Gantt chart in excel How To Create.3

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

How To Create.4

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

How To Create.5

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.

How To Create.6

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

How To Create.7

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.

How To Create.8

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.

How To Create.9

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.

How To Create.10

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.

How To Create.11

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

How To Create.12

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

 How To Create.13

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.

How To Create.14

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.

How To Create.15

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

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

How To Create.16

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

How To Create.17

We have now created a Gantt chart in Excel.

How To Create.18

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.

How To Create.19

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

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

How to create Gantt chart in excel

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.

Gantt chart in excel Example 1

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.

Example 1.1

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.

Example 1.2

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

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

Gantt chart in excel Example 1.3

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

Example 1.4

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.

Example 1.5

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.

Gantt chart in excel Example 1.6

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.

 Example 1.7

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.

Example 1.8

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.

Gantt chart in excel Example 1.9

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.

Example 1.10

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

Example 1.11

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.

Example 1.12

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

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

Example 1.13

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.

Gantt chart in excel Example 1.14

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.

Example 1.15

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 5th pattern.

Gantt chart in excel Example 1.16

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.

Example 1.17

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

Example 1.18

The Gantt chart appears as shown in the image below.

Gantt chart in excel Example 2 result

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.

Gantt chart in excel Example 2

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.

Gantt chart in excel Example 2.1

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.

Gantt chart in excel Example 2.2

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

Gantt chart in excel Example 2.3

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

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

Gantt chart in excel Example 2.4

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.

Gantt chart in excel Example 2.5

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

Gantt chart in excel Example 2.6

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

Gantt chart in excel Example 2.7

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

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

Gantt chart in excel Example 2.8

Step 9: Click on the Format tab.

Gantt-chart-in-excel-Example-2.9

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.

Gantt chart in excel Example 2.10

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

 Gantt chart in excel Example 2.11

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

Gantt chart in excel Example 2.12

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

Gantt chart in excel Example 2.13

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

Gantt chart in excel Example 2.14

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

Gantt chart in excel Example 2.15

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

Step 16: Click OK in the next two windows.

Gantt chart in excel Example 2.16

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.

Gantt chart in excel Example 3

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.
Gantt chart in excel Example 3.1
  • 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 

What is the use of the Gantt Chart in Excel?

Gantt chart helps the users track the status of various projects. It helps us to visualize the project schedule in a graphical manner.

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

The table below lists the projects of a company in column A and the number of days allotted to complete each project in columns B and C. So, we need to use the following steps to create a Gantt chart in excel.

FAQ Example 1

The steps used to create a Gantt chart in excel.

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

FAQ Example 1.1

Select the start date (column B) in the project task table.

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.

FAQ Example 1.2

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

FAQ Example 1.3

Clearly, we can see 2-D stacked bar in our worksheet.

FAQ Example 1.4

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

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

FAQ Example 1.5

Step 7: Then, the Edit Series window appears.

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

FAQ Example 1.6

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

FAQ Example 1.7

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.

Gantt chart in excel FAQ Example 1.8

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.

FAQ Example 1.9

We have now created a Gantt chart in Excel.

FAQ Example 1.10

Step 13: To add the chart title, select the Chart Title option and then, enter the chart title as ‘Project Planner’.

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

FAQ Example 1.11
Likewise, we can create the Gannt Chart in Excel.

Where is the Gantt Chart in Excel?

Unfortunately, the Gantt chart is not a built-in chart in Excel. So, we have to create a stacked bar chart and then convert it into a Gantt chart by applying various formatting techniques.

What are the features of the Gantt Chart?

The features of the Gantt chart are:
1. We must have a start date and end date of the project.
2. Also, we should have the task or phase details along with the duration.
3. Apart from these, we also need the current date highlight.

What are the limitations of the Gantt Chart?

The limitations of the Gantt chart in excel are:
1. Unfortunately, it is a tedious process to prepare Gantt chart in Excel since it is not a built-in chart by default.
2. All tasks are separated by each phase of the project, therefore, one needs to look back at the previous tasks to go phase by phase manner.
3. Bars represent the number of days a phase or a task takes to complete but not the complete hour breakdown.
4. In addition, it requires a considerable amount of Excel skill to use the template.

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.

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 – 

Reader Interactions

Leave a Reply

Your email address will not be published.