Now we want to remove the Start Date as one of our series items. Right-click on one of the segments representing the Start Date field and choose Format Data Series. Pick “No Fill” for the Fill option and “No Line” for Border Color.
Next we want to override the automatic dates that Excel has picked for our chart and use the minimum and maximum dates related to our specific project. Before we do this, we need to determine the serial numbers that are assigned to these dates in Excel.
On a “scrap” worksheet, we will enter the dates that we want to use. In this case, the dates are 12/15/07 and 12/31/08. Select the cells containing the dates and choose the Format Cells option. From the Category list, choose Number and then click OK. We obtain 39431 and 39813 as our corresponding minimum and maximum values.
The file that accompanies this tutorial in the Media Gallery contains a worksheet that will make this calculation for any date that you choose to enter. Feel free to download this file to use for later projects.
Note: In order for the entire Gantt chart to be shown, the following step is critical. For the Minimum number below, use a serial number that corresponds to a date that is on or before the beginning of your project. For the Maximum, use a serial number that corresponds to a date that is on or after the end of your project.
Return to your stacked bar chart and right-click on the Start Date axis. Under Axis Options, enter 39431 for Minimum, 39813 for Maximum, 91 for Major Unit, and 1 for Minor Unit. Entering 91 for the major unit allows the chart to be divided into blocks that represent about 3 months. The minor unit of 1 represents a single day.
Finally, we arrive at something that looks like a Gantt chart.
You can use the chart design options in Excel to fancy the final Gantt chart up a bit if you like, but the object we have here contains all the basic information.
If you're looking for an easier way to create a Gantt chart in Excel, you may want to check into some of the add-ins that have been developed. One of these add-ins, Project Manager for Excel, is reviewed here on Bright Hub's Project Management Channel. It's very reasonably priced and may be able to help with some of your other task scheduling needs.