How to create Gantt chart in excel – Part 02

This is part 2 of the Gantt charts in Excel. Please note that we shall be using formulas in this section and the result would be a table based Gantt chart. We would be using a combination of Vlookup, IF, AND formulas. If you are not confident about using somewhat complex formulas, check out the first part which uses the existing graphs in excel. Part one can be accessed here.  

Let’s look at the format of our Gantt chart. 
First, lets start creating the table starting with the smallest date and the project activities.
To look better, lets change the orientation of the date. To do this, right click on the cell that has the date  (C5 in our example) -> Format Cells -> Alignment -> change the Orientation to 90 degrees
Lets create the Gantt chart with weekly data. Add the date range by 7. In our case, let D5 be “=C5+7”, E5 be “=D5+7” etc. Or you can have increment number in one of the cell and add the number with previous date.
Now that’s the Gantt chart output format. 
Next, let’s get into the actual formulae to get the Gantt chart itself. The logic we would be using: Compare the start date with the date ranges provided & End date with the next available date range provided. The chart would be plotted based on the comparison results. 
Let’s do the vlookup first to get the start date into the output. The formulae we would be using (for this specific example):
=VLOOKUP($B6,Sheet1!$A$1:$C$7,2,0)
Please note that we haven’t formatted the results to date, so the absolute value (a.k.a “general” formatting) is done for these cells. 
Next we need to compare the “start dates” with the date ranges we had added. If the start date is less than the date ranges then the value would be 1 else the value would be 0. The formula we would use (for our example):
=IF(VLOOKUP($B6,Sheet1!$A$1:$C$7,2,0)<=C$5,1,0)
Next, to get the actual duration, the logic would be to get the end date and compare with the next available date range. In our example, we would be using vlookup for the end date and compare with next available date range (D5 onwards).
If both the logic (start date < date range and End date > next available date range) are true, then it would return 1 else 0. To add two logic into one “IF” function, we use the “AND” command. Formula used would be (for our example):
=IF(AND(VLOOKUP($B6,Sheet1!$A$1:$C$7,2,0)<=C$5,VLOOKUP($B6,Sheet1!$A$1:$C$7,3,0)>=D$5),1,0)
Now let’s use conditional formatting to convert these “0” and “1” into color bars. We don’t want the 0s – so lets click conditional formatting -> New rule -> “Use a formula to determine which cells to format”. Let’s give the condition : if the cell has 0, then the “font color” would be white color (so that the value won’t be visible). Let’s use this for the entire table (format printer)
The output would be below:
Now let’s color code the 1s as, say, blue. This time, lets “fill” the cell also with blue
Output would be our Gantt chart!!
Now, you may realize the last date range was having “1” and now the blue though none of the project activities were stretching till the last date – this is because of our formula. As we were comparing the end date with the next available range, excel would take the next cell for the last column as 01-01-1900! We can fix this by adding another IF statement. The formula would be (in our example):
=IF(D$5=0,0,IF(AND(VLOOKUP($B6,Sheet1!$A$1:$C$7,2,0)<=C$5,VLOOKUP($B6,Sheet1!$A$1:$C$7,3,0)>=D$5),1,0))

The final output would be without the blue bar in the last column (unless there is actual data for the same)
I do realize that this method is bit complex with multiple formulas involved – once we are accustomed with the logic, then it becomes easy.
I hope this was useful and you like this tutorial. Please share your valuable feedback which will help in improving the content. Thanks for reading. 

Leave a Reply

%d bloggers like this: