How to Build an Automatic Gantt Chart in Excel?

Last Updated : 11 Mar, 2022

Gantt Chart is the most commonly used chart in project management. The more general definition of the Gantt chart is that it helps to keep track of the activities against time. Anything plotted against time can be represented in the form of a Gantt chart. For example, a team manager has given different tasks to their team members. Now keeping track of every task manually is a very difficult process but this can be easily achieved by the Gantt charts. With the help of Excel functions and Charts, we can automate the Gantt chart and so the progress of a project.

Number representation of Date in Excel

Before learning the Gantt chart in excel, you need to know how to format date in Number format in excel. For example, 3/13/2022 is in the date format and when converted to Number the value will be 44633.00. The use of the Number representation of Date might not seem very useful now, but when we will create a Gantt chart then the use of it will be very clear. Following are the steps:

Step 1: Given a Date in cell C4. We can see that the format of the cell is a date.

Step 2: Go to Home Tab, and select the drop-down menu in the Numbers section. Select the Number data type.

Step 3: You can see the value of date 3/13/2022 in number is 44633.00.

Creating an Automatic Gantt Chart in Excel

Creating an automatic Gantt chart using simple horizontal bar charts. For example, Rohan wants to keep track of the Geeks for Geeks courses he is studying. Given a data set of Geeks for Geeks courses, its start time, and the number of days required to complete a particular course. Create a Gantt chart for it. The chart would be an automatic Gantt chart which changes with a change in the value of Start-Date and Days to Complete.

Step 1: Go to Insert Tab, and in the charts section, click on the bar chart.

Step 2: An empty chart is created.

Step 3: Right Click inside the blank chart. A drop-down appears. Click on Format Chart Area.

Step 4: Select Data Source dialogue box appears now click on Add button.

Step 5: An Edit Series dialogue box appears.

Step 6: In the Series name column. Select cell C5.

Step 7: In the Series Values column. Select the range C6:C12. Click Ok.

Step 8: Start-Date got added. Again click on Add button to add Days to Complete.

Step 9: The Edit Series dialogue box appears. In the Series name column. Select cell D5.

Step 10: In the Series Values column. Select the range D6:D12. Click Ok.

Step 11: Now click on Edit Button to add Geeks for Geeks Courses.

Step 12: An Axis Labels dialogue box appears. Select the range B6:B12. Click Ok.

Step 13: The Select Data Source dialogue box reappears. Click Ok.

Step 14: A basic bar chart is created. Now we can figure out there are three problems to converting a bar chart to a Gantt chart.

The courses appear in reverse order. The Excel Course should be at the top and the Git and Github should be at the bottom.
The Blue Bar Lines should not be there in the Gantt chart.
The earliest date in the given data set is 15-Jan. So the graph should start from 15-Jan but it is starting from 10-Dec. So, you need to remove all the redundant dates.

Solving all the problems one-by-one.

Step 15: Right-click inside the Y-axis i.e. inside the courses section. A drop-down appears and clicks on Format Chart Area.

Step 16: A Format-Axis dialogue box appears. In the Text Options, check the box Categories in reverse order. Now, all the courses will be reversed.

Step 17: The Labels got reversed.

Step 18: Right Click on the Blue bar charts. A drop-down appears. Click on Format Data Series.

Step 19: Format Data Series dialogue box appears. Go to Fill and Line, inside the fill section. Click on No Fill. All the blue bars will disappear.

Step 20: The only work left is to remove the redundant dates from the chart.

Step 21: The topic studied at the start of the article i.e. Number representation of Date in Excel will significantly help you to remove the additional dates in the Gantt chart. In the given data set, you can see that the earliest date is 15-Jan. Now, you need to know what is the numerical value for 15-Jan. The value for 15-Jan in excel is 44576.00. Remember this number.

Step 22: Now, inside the date section of the chart, right-click on it. Click on Format Axis.

Step 23: Format Axis dialogue box appears. In the Text Options section, click on the minimum value. Replace 44540.00 with 44576.00.