Excel: How to Use Gannt Chart to Display Event Timeline of a Job

A Gantt chart is a type of bar chart^[1] that illustrates a project schedule. Gantt charts illustrate the start and finish dates of the terminal elements and summary elements of a project.

In this article, we will use Gannt chart to display the event timeline of a parent job (i.e., "Creating Application"), which was processed by its child jobs asynchronously.

Gantt Chart in Excel

As shown in [2, 3], there is no direct support of Gantt Chart in Excel.  However, you can use Stacked Bar to achieve the same effect.  In both referenced articles, they use date and duration (in days) as example.  Here we will show you how to use time and duration (in hh:mm:ss) to draw the Gantt chart.

	Start Time	End Time	Duration (in secs)	Duration (in hh:mm:ss)
pool-get-vm-from-iaas	8:49:57	8:52:11	134	0:02:14
otd-add-app-config	8:49:52	8:49:57	5	0:00:05
scale-composite-operation	8:49:57	8:54:03	246	0:04:06
create-image-and-start-instances	8:49:57	8:54:03	246	0:04:06
otd-add-node-to-app-config	8:54:01	8:54:03	2	0:00:02
Creating Application	8:49:51	8:54:03	252	0:04:12

If you select the cell "8:49:57" and check its format by selecting "Format Cell", you would find that Excel automatically assign the "h:mm:ss" format to the cell.

The main issue of drawing Gantt Chart in hh:mm:ss format is that

How would you represent your duration? 

To start with, I have tried the following formats:

• 02:14
• 134 (secs)
• 00:02:14

By trial-and-error, it turns out that Excel only like the third format.  If you chose the first format, Excel would treat it as 02 hours and 14 minutes.

Drawing Steps

Without much ado, I would urge you to watch this video^[2] for the how-to.  Here we only highlight the steps that are unique to this Gantt Chart.

You can insert a bar chart by clicking Insert > Bar > Stacked Bar, and a blank chart will be created as below.

Then you add the data series to the chart by selecting the blank chart and right clicking, followed by choosing Select Data from the context menu as shown above.

In the Select Data Source dialog, click Add button under Legend Entries(Series), and then an Edit Series dialog will appear:

1. Click button under Series name to select the Start Time cell, B1 in this case.
2. Click button under Series values to highlight your time cells (from cell B2 to cell B7 in this example), see below:

Click OK to return to the Select Data Source dialog, then repeat the step to continue adding Duration data series (i.e., Duration (in hh:mm:ss)) to the chart. When the Start Time and Duration data series are inserted into the chart, you will get the following chart:

Next, you need to hide the Start Time legend, select one blue bar and right click, then choose Format Data Series. In the Format Data Series dialog, click Fill from the left pane, and select No fill option, then go on clicking Border Color, and select No line option, the final screenshot should look like:

In the next step, you would like to convert the numerical step on the left side of the chart into a list of tasks. Click on axis labels in the chart, then right click and open Select Data. Under Horizontal (Category) Axis Labels, click on edit. Using your mouse, highlight the names of your tasks.

You're almost finished. You just need to remove the empty white space at the start of your Gantt chart. Click on the first Start Time (i.e., 8:49:51) in your data table. Right click over it, select Format Cells, then General. Write down the number you see. In my case it is 0.367951389. Hit Cancel because you do not want to actually make any change here.

Repeat the step to see the number for the last end time (i.e., 8:54:03) of your time series.

In the Gantt chart, select the time labels below the bars, right click and choose Format Axis.  Change Minimum to Fixed and enter the number you recorded (i.e., 0.367951389). Do the same for Maximum by entering 0.370868056. Change Major unit to Fixed and enter the number 0.0005. You can play with this  delta to see what works best for you.

When you are finished, your Gantt chart should look like this:

Tips

1. Make sure both your "Start Time" and "Duration" cells have the "h:mm:ss" format:
• Format Cell > Custom > h:mm:ss
2. You can reverse the order of Y axis by selecting "Categories in reverse order":
• Click on axis label > Right click and select Format Axis > Axis Options > Categories in reverse order
3. You might want to reorder rows to reveal the order of tasks.  
1. For example, otd-add-app-config is the first task executed in the above diagram and other tasks (except the parent task "Creating Application") depending on it.  So, it might be a good idea to move it to be the first row and you can remove "Creating Application" parent task, etc.

References

1. Gantt Chart
2. How To...Create a Basic Gantt Chart in Excel 2010
3. How to create Gantt chart in Excel?