Abrar-ur-Rahman Niloy
Gantt chart is one of the most popular ways of representing tracks of tasks or events. It is very useful in project management. Despite being a popular tool for tracking and analyzing data, Excel doesn’t have ways to create a Gantt chart directly. But we can use some of the handy tools in Excel to assemble one easily. In this tutorial, we will focus on how to make a Gantt chart with multiple start and end dates in Excel.
Download Practice Workbook
You can download the workbook with the dataset and the Gantt chart of the demonstration from the link below.
What Is Gantt Chart?
The Gantt chart was popularized by Henry Gantt. It is a type of bar chart that illustrates or tracks a project schedule. There is a list of activities or tasks on the left of the bar chart and on the horizontal axis, there exists a suitable time frame. Here is what a Gantt Chart usually looks like.
A Gantt chart can be used to figure out:
- Total different activities
- When each of them starts or ends
- How long they last
- If there are any overlaps or gaps in between them
Step-by-Step Procedure to Create Excel Gantt Chart with Multiple Start and End Dates
The main goal to create a Gantt chart both with and without multiple start and end dates is to plot a stacked bar chart initially. Then make the unnecessary bars invisible. This way we can replicate a Gantt chart in Microsoft excel. Even though we have a start and end date in datasets, we need periods to create Gantt charts. So we need to find out durations first.
For example, let’s take a dataset where there are multiple projects with multiple start and end dates.
Step 1: Calculate Duration of Each Project
We first need to find out the time period for each project. Because the bars in a Gantt chart always represent the total duration of each segment. Follow these steps to calculate the duration.
- For that, let’s select cell E5 and write down the following formula.
=D5-C5
- After pressing Enter, you will have the duration of the first project.
- Now select the cell again and click and drag the fill handle icon down to replicate the formula for the rest of the cells.
Step 2: Create Stacked Bar Chart
As mentioned earlier, a Gantt chart is a form of bar chart and we create it from stacked bar charts in Excel. So we need to insert a stacked bar chart first in order to plot it.
- To insert a stacked bar chart, select a cell and go to the Insert tab on your ribbon.
- Then select the Insert Column or Bar Chart option from the Charts group.
- After that, select Stacked Bar from the 2-D Bar section of the drop-down menu.
As a result, a blank chart will appear on top of the spreadsheet at the cell you selected.
Read More: How to Show Dependencies in Excel Gantt Chart (2 Easy Methods)
Step 3: Select Data for Stacked Bar Chart
We created a blank chart in the previous step because we have to manually reconfigure all the labels and axis in it either way. In this step, we are going to manually enter each data for this.
- First of all, right-click on the chart area.
- Now select Select Data from the context menu.
- As a result, the Select Data Source box will appear. Now click Add under Legend Entries first.
- Now in the Edit Series box, select cell C4 for Series name and select the range C5:C12 for Series values. Then click on OK.
- After that, click on Add again under Legen Entries in the Select Data Source box.
- This time, select cell E4 as the Series name and the range E5:E12 as the Series values. Then click on OK.
- Next, click on Edit under Horizontal Axis Labels in the Select Data Source box.
- Now select the range B5:B12 as the Axis label range in the Axis Labels box and click on OK.
- Finally, click on OK in the Select Data Source box.
As a result, the graph will look like this now.
Read More: How to Add Milestones to Gantt Chart in Excel (with Quick Steps)
Step 4: Reverse Order of Categories
From the graph created at the end of the previous step, we can see that the project orders are in the opposite order than required.
- To change it back to the required position, double-click on the axis. This will open up the Format Axis window on the right of the spreadsheet.
- Now select the Axis Options section in the Axis Options tab.
- After that, check the Categories in reverse order option.
Now the graph will look something like this.
Step 5: Readjust Horizontal Axis Label Position
After rearranging the project lists in the previous step, the horizontal axis labels will move up.
- To move it down again, double-click on the axis label.
- Similar to the previous step, the Format Axis window will pop up on the right of the spreadsheet. Now go to the Axis Options tab and under the Labels section, change the Label Position to High.
Hence, the horizontal axis label will automatically shift down to its original position again.
Step 6: Remove Fills and Borders of Start Dates
A standard Gantt chart only represents the duration and starting point of a task. But the chart we have created has both the starting date as well as the durations plotted from a base starting date of 7/23/2021. We need to remove these first bars. In other words, we need to withdraw the blue lines in our graph.
- To do that, double-click on any of the blue bars in the chart.
- Thus the Format Data Series window will open up on the right of the spreadsheet. Select the Fill & Line tab here.
- Then select No fill for both the Fill and Border sections.
Now the chart will look closer to a Gantt chart.
Step 7: Change Maximum and Minimum Values of Horizontal Axis
The chart, at this point, seems to start and a random place and ends at one. We need to modify it more to look more like an ideal Gantt chart that starts and ends with the horizontal axis. To do that, we need to change the maximum and minimum values of the horizontal axis.
- To determine the maximum and the minimum value of the axis, we need to find when the earliest project starts and the latest project ends. These dates are located in cells C5 and D12 in our dataset.
- After identifying the dates, copy and paste the values to another cell. We have pasted them in cells C14 and C15 for demonstration.
- Now select the cells and change the cell format to General. You can find it in the Number group of the Home tab.
- Now the values will look something like this.
- To change the chart label values to these, double-click on the horizontal axis label.
- As a result, the Format Axis window will open up within the Excel spreadsheet. Now go to the Axis Options tab in it.
- Next, put the values obtained in the value boxes under the Axis Options section.
The chart will look like this now.
Step 8: Format Gantt Chart
At this point, the chart can be called a Gantt chart. Since it represents all the key features of a Gantt chart. But it is always a good idea to modify it to make it more clear and more presentable. For example, we can change the date format of the horizontal axis by these steps.
- First, double-click on the horizontal axis label.
- Then the Format Axis window will open up again. Go to the Axis Options tab in it.
- Then under the Number section, you can find the Format Code Now type in any format you like. We have selected dd-mmm-yy as the date format.
- After selecting the format, click on Add.
The graph label will change now.
We can also make the bars thicker by double-clicking them first. Then You can change Gap Width from the Series Options tab to a lower value.
This way the bars will look like this.
You can change the fill color/style from the same Window. But this time, go to the Fill & Line tab and you can select your style under the Fill section. We have selected gradient fill as our style here.
You can also add labels by selecting the chart and clicking on the Chart Elements icon that appears on the right. Then select Data Labels.
The chart will finally look like this.
At this point, we can say we have successfully created a Gantt chart with multiple start and end dates in Excel.
Read More: How to Create Excel Gantt Chart with Multiple Start and End Dates
Conclusion
That concludes our tutorial for creating a Gantt chart with multiple start and end dates in Excel. Hopefully, you can create Gantt charts on your own with ease now. I hope you found this guide helpful and informative. If you have any questions or suggestions, let us know below.
For more guides like this, visit Exceldemy.com
In Step 6 (Step 6: Remove Fills and Borders of Start Dates), where did the date of 7/23/2021 come from?
Greetings ALAN,
I appreciate you asking this question. Excel will automatically generate this date in the intermediate stage when you are trying to create a gannt chart. During the Final stage, you will notice that project-1’s starting date has been corrected.
Hi, this was a clear guideline, thank you. I have an additional question. I want to have different bars for 1 project behind each other. Is that possible?
For example
project start end duration start end duration
1 1.1. 31.1. 31 1.6. 30.6. 30
2 1.1. 31.3. 89
Hi Elly,
For your data, plotting a stacked bar chart would not be possible. Excel needs a dataset of the following sort to plot a stacked bar chart.
1st Week 2nd Week 3rd Week 4th Week
1st Task 44 41 61 58
2nd Task 38 52 53 64
3rd Task 42 48 50 56
4th Task 37 59 44 46
Even in that case, creating a Gantt chart would not be appropriate, as one bar will start where the previous one ended. This will ultimately result in the same bar plot. But if you have a gap between each work period, you can put them in between these values and follow the steps of the article.
Thanx very Much,
Your information has helped me to gain Favor from My Boss Since i had No Idea About this chart and it was badly wanted for Information reporting!
Dear Ronald,
You are most welcome.
Regards
ExcelDemy