How to Create Excel Gantt Chart with Multiple Start and End Dates

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.

excel gantt chart 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.

excel gantt chart multiple start and end dates

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.

excel gantt chart multiple start and end dates

  • As a result, the Select Data Source box will appear. Now click Add under Legend Entries first.

excel gantt chart multiple start and end dates

  • 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.

excel gantt chart multiple start and end dates

  • After that, click on Add again under Legen Entries in the Select Data Source box.

excel gantt chart multiple start and end dates

  • This time, select cell E4 as the Series name and the range E5:E12 as the Series values. Then click on OK.

excel gantt chart multiple start and end dates

  • Next, click on Edit under Horizontal Axis Labels in the Select Data Source box.

excel gantt chart multiple start and end dates

  • Now select the range B5:B12 as the Axis label range in the Axis Labels box and click on OK.

excel gantt chart multiple start and end dates

  • Finally, click on OK in the Select Data Source box.

excel gantt chart multiple start and end dates

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.

excel gantt chart multiple start and end dates

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.

excel gantt chart multiple start and end dates

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.

excel gantt chart multiple start and end dates

  • 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.

excel gantt chart multiple start and end dates

The chart will look like this now.

excel gantt chart multiple start and end dates


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.

excel gantt chart multiple start and end dates

The graph label will change now.

excel gantt chart multiple start and end dates

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.

excel gantt chart multiple start and end dates

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.

excel gantt chart multiple start and end dates

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


Related Article

Abrar Niloy

Abrar Niloy

Hi! my name is Abrar-ur-Rahman Niloy. I have completed B.Sc. in Naval Architecture and Marine Engineering. I have found my calling, if you like, in Data Science and Machine Learning and in pursuing so, I have realized the importance of Data Analysis. And Excel is one excel-lent tool do so. I am always trying to learn everyday, and trying to share here what I am learning.

4 Comments
  1. In Step 6 (Step 6: Remove Fills and Borders of Start Dates), where did the date of 7/23/2021 come from?

  2. 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.

  3. 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.

Leave a reply

ExcelDemy
Logo