If anyone wants to keep track of ongoing or upcoming tasks or projects, then using an Excel Gantt Chart will help them in this regard. Sometimes, users might be confused about making an Excel Gantt Chart, as there are no direct ways to create this chart. In this article, we will show you how to use Excel Gantt Chart.
Download Practice Workbook
You can download the free Excel workbook from here and practice on your own.
Step-by-Step Procedures to Use Excel Gantt Chart
An Excel Gantt Chart is a handy tool for tracking the tasks of a project or event. The users get an overall idea of the tasks from this chart. For example, how many tasks took place, what were the start and finish times, which tasks required how much time, etc. In this article, you will get the step-by-step procedures to use Excel Gantt Chart. Also, you will be able to customize the Gantt Chart by implementing different features of Excel.
Step 1: Selecting Data Set
To use the Excel Gantt Chart, we need to select our data set first. For that,
- First of all, select the following data set.
- In the data table, we have Task No., Start Date, and End Date.
Step 2: Determining Duration for Each Task
After selecting the data set, now we will determine the duration for each task. Go through the following steps to calculate the duration.
- First of all, determine the duration of Task-1 in cell E5 by applying the following formula.
- Secondly, press Enter and you will get the duration in cell E5, which is 94 days.
- Finally, use the AutoFill to drag the above formula to the lower cells in column E to calculate duration for all the tasks.
Step 3: Applying Stacked Bar Command
We have to apply the Stacked Bar command to draw the Gantt Chart. In order to do that, follow the following steps.
- Firstly, select the data range from cell C4 to cell C12 which is the Start Date column in our data set.
- Then, go to the Insert tab of the ribbon.
- After that, choose Insert Column or Bar Chart command from the Charts group.
- From the drop-down menu, choose the Stacked Bar command under the 2-D Bar section.
- Finally, after the whole process, you will get the following chart.
- Then, name the chart “Excel Gantt Chart”.
Step 4: Inserting Data into Stacked Bar Chart
In the above chart, there is only one series which is the Start Date from our data set. Now, we have to insert one more data series to our chart.
- First of all, right-click on the chart and click on the Select Data command.
- Secondly, you will see a dialogue box that’s name is Select Data Source.
- Then, click on the Add option from that box.
- After clicking, you will see a new dialogue box named “Edit Series”.
- You will also notice two blank spaces in that box.
- First of all, in the “Series name” type box, insert cell E4 which is the column name of the new data series.
- Secondly, select cell range E5 to E12 from the data table in the “Series values” dropdown as the values.
- Finally, press OK after fulfilling all the criteria.
- Thirdly, click on the Edit command which is under the Horizontal (Category) Axis Labels heading.
- Fourthly, select the cell range B5:B12 as the Axis label range in the Axis Labels box.
- After that, press OK.
- After completing all of these, the Select Data Source dialogue box will appear.
- Then, press Ok.
- Finally, you will get the following chart after completing all the steps.
Step 5: Arranging Categories in Reverse Order
In the chart from the previous step, we can see that the task sequences are in reverse order.
To rearrange the sequence, follow the following steps.
- First of all, double-click on the axis.
- Then, a new window naming Format Axis will appear right beside the chart.
- Thirdly, under the Axis Option label, mark the Categories in reverse order command.
- Finally, you will find all the categories arranged in reverse order.
Step 6: Positioning of Horizontal Axis Label
You will see the horizontal axis label on top of the chart after performing the previous step.
Now we have to position the labels back in their previous location. To do that, go through the following steps.
- Firstly, double-click on the axis label.
- Then, you will notice that the Format Axis window pane will appear again.
- Thirdly, go to the Labels section under the Axis Options tab.
- Fourthly, from there to the Label Postion option choose the Label Position as High.
- Finally, you will see the horizontal axis label at the bottom of the chart.
Step 7: Eliminating Fills of Start Date in Stacked Chart
We need to eliminate the blue bars from the chart, as the Gantt Chart represents only the duration of a task from the start date. To eliminate the blue bars, follow the following steps.
- First of all, select any blue bars from the chart by double-clicking on it.
- Then, a window pane named Format Data Series will appear on the right side of the chart.
- Thirdly, choose the No fill command under the Fill & Line tab.
- Finally, you will get the Gantt Chart which will look like this.
Step 8: Adjusting Minimum and Maximum Values of the Horizontal Axis
If you look at the chart from the previous step, you will find it difficult to understand. Because it seems to start and end from any random place. To modify it, we have to add the minimum and maximum values of the horizontal axis. Go through the below steps to do that.
- First of all, we will create two cells in C14 and C15 naming Minimum Value and Maximum Value .
- Then, we will find the earliest task date and the latest date when a task has been ended.
- Thirdly, we will add them to those cells accordingly.
- Then, we will change the format of the dates to General.
- To do that, go to the Home tab of the ribbon.
- Thirdly, choose the General command from the Number group.
- Finally, the format of the date will look like this.
- To adjust the label values in the chart, double-click on the horizontal axis label.
- After that, the Format Axis window pane will appear.
- Thirdly in the Axis Options tab input the Minimum and Maximum values manually that we calculated at the beginning of this step.
- Finally, you can observe the following Gantt Chart.
Customizing Gantt Chart
The process of creating an Excel Gantt Chart is over. But, if you want you can customize your Gantt Chart and make it more attractive and understandable.
1. Changing Gap Width of Gantt Chart
Now, we will demonstrate to you to change the gap width of the bars in the chart. To do so follow the following steps.
- Firstly, double-click on the red bars from the chart.
- Then, from the Format Data Series window pane, change the Gap Width under the Series Options label.
- If you decrease the percentage, then the bar will get thicker. It will get thinner in case you increase the percentage.
- For our graph, we will decrease the percentage.
- Finally, our Gantt Chart will look like this.
2. Altering Colors and Styles of Gantt Chart
If you want to alter the colors and styles of your chart, then there are some ways for doing that. You can see those ways in the following.
- Firstly, double-click on any of the bars on the chart.
- Then, go to the Fill & Line command from the Format Data Series window pane.
- From there, choose the Gradient fill command under the Fill label.
- Also, choose the shade of your preference from the Preset gradients command.
- Finally, we will get our Gannt Chart like this.
3. Adding Data Labels in Gantt Chart
The Gantt Chart becomes more understandable if it has data labels in it. You can easily add the data labels to the chart. Just go through the following steps.
- First of all, click anywhere in the chart and three icons will appear after clicking on the chart.
- Secondly, from those icons, select the Chart Elements command.
- Then, after clicking the command, you will see many other features.
- Fourthly, select the Data Labels option from those features.
- Finally, you will see data labels on all of the bars.
- To make the data labels more user-friendly, we will customize them.
- For that, click on the data labels on the chart.
- Then, you will see the Format Data Labels window pane.
- From there, choose the Solid Fill command under the Fill label in the Fill & Line tab.
- Finally, the Gantt Chart will look like this.
That’s the end of this article. I hope you find this article helpful. After reading this article, you will be able to use Excel Gantt Chart. Please share any further queries or recommendations with us in the comments section below.