Your Free Project Scheduling and Gantt Chart Tool Using Microsoft Excel
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

# 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=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

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