How to create Gantt chart in Excel – Part 01

Gantt chart is one of the frequently used tool in project management to illustrate the project schedule. This chart is extremely popular that most of the companies are adopting this for various purposes. Lets see a step by step process on how to create Gantt chart in excel. 

Note: We can do this either by existing charts or by complex formulae. Hence I have made this topic in to two parts. First we shall see the easier way using the stack chart available in Excel. 
Lets take a simple table with various project items and its start and end dates.
Please note the duration is a simple “End-date” minus the “Start-Date”
Next, lets insert the chart by the following steps: Insert -> under charts, 2D bar -> Stacked bar.
Lets add some data to the inserted chart. Right click on the chart area and choose “Select Data”
In the “Legend Entries (Series)”, click on “Add” and select the “start date” and “Series”. Shown below:
Once you add both the entries, the graph should look like below:
Now, lets add the project entries. Again, right click on the chart area and choose Select Data.
On the “Horizontal (Category) Axis Labels”, click on “Edit”
Add the project activities. Please note: Do not select the column heading in any of the data selection.
Graph should look like below. This is a simple stack graph up till now. After this we shall convert this stack chart into a Gantt chart.
Click on the blue filled series in the graph, then right click on the same and choose “Format Data Series”
Under “Fill & Line” option, Click on “No Fill” as shown below
Now, only the Orange series is visible in the chart and our Gantt chart is starting to show up. But doesn’t look pretty as there are lot of empty spaces in the first section of the chart. 
Lets remove those empty spaces now. Click on the date series and select “Format Axis”

Before we change the axis, we need to find out the smallest date value in the “start date” series. If the data set is less, finding the smallest value is easy. Else use the “Small” formulae. Syntax would be 

Here, array is the list. k value is nth smallest value. For example if you want the 1st smallest value, k would be 1 or if you need the 2nd smallest value, then k would be 2. Example provided below.
Going back to “Format Axis”: The axis options would be in numbers rather than actual dates. Converting them is easy. Take the smallest date (in our example – 21st Feb 2019) and change the format of the cell to “General” from “Date”. 21st Feb 2019 will be converted to 43517. Enter this value in the “Minimum” option of the “Axis Options”. Shown below
Once this is entered, our Gantt chart is ready as shown below
I hope you like this tutorial. Please don’t forget to tune in for the 2nd part of this topic.  
Please provide us with your valuable suggestion which would help to improve our content. Thanks for reading. 

Leave a Reply

%d bloggers like this: