Open In App

How to Build an Automatic Gantt Chart in Excel?

Improve
Improve
Like Article
Like
Save
Share
Report

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. 

Given-a-Date-in-cell-C4

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

Select-the-Number-data-type

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

You-can-see-the-value-of-date-in-number

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

Days-to-Complete-GFG-courses

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

Click-on-the-bar-chart

Step 2: An empty chart is created. 

An-empty-chart-is-created

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

Click-on-Format-Chart-Area

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

Select-Data-Source

Step 5: An Edit Series dialogue box appears.

Edit-series-dialogue-box

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

Select-cell-C5

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

Select-the-range-C6:C12-then-click-Ok

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

Start-Date-got-added

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

Select-cell-D5

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

Select-the-range-D6:D12-and-click-Ok

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

Add-Geeks-for-Geeks-Courses

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

Select-the-range-B6:B12-and-click-Ok

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

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. 

  1. The courses appear in reverse order. The Excel Course should be at the top and the Git and Github should be at the bottom.
  2. The Blue Bar Lines should not be there in the Gantt chart.
  3. 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. 

A-basic-bar-chart-is-created

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

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. 

Check-the-box-Categories-in-reverse-order

Step 17: The Labels got reversed. 

The-Labels-got-reversed

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

Right-Click-on-the-Blue-bar-chartsClick-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. 

Click-on-No-Fill

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

Remove-additional-dates

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. 

Add-number-format

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

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

Replace-44540.00-with-44576.00.

Step 24: An automatic Gantt chart is created. This chart is a dynamic chart that could adjust itself with a change in values in the data set. 

Automatic-Gantt-chart-is-created



Last Updated : 11 Mar, 2022
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads