How to create a Gantt template in Excel?

Normally we make project schedules with Gantt chart, so that readers can know when a project starts and ends at a glance. In this article, I will show you how to create a Gantt chart by Conditional Formatting feature in Excel, and save the Gantt chart as an Excel template.

Create a Gantt table and save as normal Excel template

Save the Gantt table (selection) as mini template

Create a Gantt Chart with conditional formatting, and then save as an Excel template

The following steps will walk you through creating a Gantt chart by conditional formatting, and saving it as a template in Microsoft Excel.
Step 1: Prepare a table in a blank workbook, and enter your data into this table as following screen shot shown:

Step 2: In Cell B1 enter a start date, such as 11/1/2014. And enter your data into corresponding cells one by one.

Step 3: Calculate Duration days. In Cell D3 enter =C3-B3+1, and drag the Fill Handle down to apply this formula to the range you want.
Step 4: Add dates into this table.

(1) In Cell F2 enter =B1, and press the Enter key;

(2) In Cell G2 enter =F2+1, and drag the Fill Handle right to apply this formula to the range you want. In Our case, we apply this formula in Range G2:AI2.

(3) Select the date cells of Range F2:AI2, click the Anchor in the Number group on the Home tab, and in Format Cells dialog box click Custom in Category box, then enter D into the Type box, and click the OK button. See screen shot below:

Step 4: In Cell F3 enter =IF(AND(F$2>=$B3,F$2<=$C3),""), and drag the Fill Handle to the Range F3:AI6.

Now you will see the date columns are filled by False and blank. See screen shot below:

Step 5: Select the Range F3:AI6, and click the Home > Conditional Formatting > New Rule.

Step 6: In the coming New Formatting Rule dialog box,

(1) Click to select the Use a formula to determine which cells to format in the Select a Rule Type box;

(2) In the Format Values where this formula is true box, enter =F3="";

(3) Click the Format button.

Step 7: In the Format Cells dialog box, go to the Fill tab, and specify a background color, and then click both OK buttons to close the Format Cells dialog box and New Formatting Rule dialog box.

Step 8: Keep selecting the Range F3:AI6, and hide fonts in the range by clicking Home > Font Color > White or any color same as your background color.

Now you will see the Gantt Chart made by conditional formatting as the following screen shot shown:

Step 9: Click the File > Save > Computer > Browse in Excel 2013, or click the File /Office button > Save in Excel 2007 and 2010.

Step 10: In the coming Save As dialog box, enter a name for this workbook in the File name box, and click the Save as type box and select Excel Template (*.xltx) from drop down list, at last click the Save button.

Related articles: