Your Free Project Scheduling and Gantt Chart Tool Using Microsoft Excel
main introduction download help pages excel vba useful resources books sponsor / advertise forum author's profile
XL-EasyGantt

  One of the most common challenge in using Microsoft Excel as a project scheduling tool is the plotting of the Gantt chart.

Excel tables provides a very convenient way of entering the tasks and dates, however, without certain level of automation, the plotting of the Gantt chart becomes a tedious task of either manually coloring each cell or using the drawing tool to draw rectangles to the right of the dates to simulate the charts.

XL-EasyGantt was designed with the idea to strike a balance between the automatic plotting of Gantt chart without trying to recreate an extensive tool to replace Microsoft Project. In this template, a good degree of freedom is provided for you to customize the way you want the worksheet to look like. What is important to know is the formula used and you would be able to play around the format pretty easily.

There are some simple and yet interesting methods which I use in XL-EasyGantt to achieve its result. Some of the formulas are highlighted here.

 
More Formulas

§ formula for chart plotting

§ calculating calendardays

§ calculating networkdays

§ highlighting weekends

§ conditional formatting
 
 

Automatic Plotting of the Gantt Chart

To generate the Gantt Chart automatically based on the Start Date (column D) and End Date (Column E), each cell within the chart area is set with a formula to check if it falls within the date range between the Start Date and End Date by checking the cell related Calendar Date in Row 3 directly above it. Together with the use of Condition Formatting, and the symbol entered in column H, the color of the cell can be then be decided.

Example:

The formula highlighted in cell L6 is:

=IF(OR(AND($D6>=L$3,$D6<M$3),AND($E6>=L$3,L$3>=$D6)),IF(AND($H6="x",$E6>=L$3,$E6<M$3),"x",IF($H6=".",".","..")),"")

A series of nested IF, AND, OR are being used to achieve the result. This formula is designed to allow the Gantt Chart to be plotted even if the calendar is using a Weekly scale rather than a daily scale. The best estimate length of each bar will be plotted if ia weekly scale is being used.

The above formula is reorganized in a clearer presentation below:

IF(      
  OR(   Logical Test
   
AND($D6>=L$3,$D6<M$3),
 Start date must be greater or falls on the same date as the Chart Cell date. Start date must be less than the next Chart Cell date.
      OR
   
AND($E6>=L$3,L$3>=$D6) 
 
 End date must be greater or equal to Chart Cell date, Chart Cell date must be greater or equal to the Start date.
 
), 
 
    
       
 
IF( 
 
   If True
      Logical Test
   
AND($H6="x",$E6>=L$3,$E6<M$3), 
 
 Format Cell is a "x" and Start Date is greater than Chart Cell date, and End date is less than next Chart Cell date.
      Than
   
"x", 
 
 Put a "X" in Chart Cell
      Else
   
IF($H6=".",".","..") 
 
 Put a "." If Format Cell is "." Else put a ".." in Chart Cell
 
), 
 
    
      IF False
 
"" 
 
   Return Blank
       
) 
 
      

 

The conditions for the Conditional Formatting applied to the cell is as below:

Next: Calculating Calendar Days (Duration) and Networkdays >>