Select Page

Just try to search out “Gantt Chart” on web once; you will get endless information. If I summarize the same – “Gantt Chart” is a type of bar chart devised by an American engineer and management consultant to represent timeline of different activities of a project.

Nowadays number of softwares are available for project management with whom you can create Gantt chart with a lot of extra information like how the activities are dependent, resources allocated to an activity etc.

Let’s get back to the core of today’s post. How to create a simple Gantt chart in excel using conditional formatting. First of all have a look, what we are going to create today. I will suggest first to DOWNLOAD the excel file containing this Gantt chart for better understanding.

Gantt Chart with conditional formatting

As you can see in the picture above, first four columns contains the name of the activity, time required to complete the activity and start & end date to complete the activity. Number of other columns depends on the number of days required to complete all the activities. Each column represent a day of the activity. First column contains the start date of the first activity and last column end date of the last activity.

To keep the chart compact all dates have been formatted as “d” to display date only. Month name is appearing over the start date of the fist activity and when the month got changed. Green bars showing the period of activity. Red columns showing the weekend i.e. “Saturday” & “Sunday”. Highlighted column with “TODAY” mentioned in bottom showing the today’s date.

All the cells against activity contains the below formula:

=IF(AND(\$D\$8<=F\$6,\$E\$8>=F\$6),2,1)

The formula checks whether the column date lies in the range of dates specified for the activity, if yes the cell is updated with “2”, if not with 1. Number “1” or “2” not appearing in the cells as we have used custom format “;;;”. Finally we fill all the cells with value “2” by green color to create time bar with below formula applying to the relevant range:

F8=2

To mention name of the month over 1st date column we used below formula and tilt it to 45 degree:

=TEXT(F6,”MMMM”)

Other than 1st date column we have to mention month name over 1st date of the month, for this we have used below formula with orientation at 45 degree:

=IF(DAY(G6)=1,TEXT(G6,”MMMM”),””)

As you can see “TODAY” is appearing below the today’s date, for this we have used below formula and aligned it at 90 degree:

=IF(F\$6=TODAY(),”TODAY”,””)

We have selected the pattern filling as appearing in today’s column and apply the below formula:

=F\$6=TODAY()

To highlight weekend columns we have selected red pattern and changed font color to red, please find below formula used for the same:

=OR(WEEKDAY(F\$6,1)=1,WEEKDAY(F\$6,1)=7)

In case you have any query regarding above, you may send the same as comment to this blog.

We should not give up and we should not allow the problem to defeat us.
– A. P. J. Abdul Kalam

By Rajesh K Saraogi

Rajesh is a hardcore finance person with a penchant for latest technology. His passions is to improve the productivity with simple, yet powerful software – mainly on Microsoft Excel platform. He is a qualified MCA. Whenever he gets time he loves to blog, to share the knowledge, which is the only thing which increases after sharing!