Using Excel to Create a Gantt Chart

click to enlarge

For a more in-depth look on how useful this device can be and for additional resources in other software applications, please see the guide Gantt Chart Examples and Tutorials.

In this tutorial, we'll take a look at how to construct a Gantt chart in Excel 2007. This article is part of a series of Excel project management tutorials that can be found here at Bright Hub.

The first thing you want to do in preparation for creating a Gantt chart in Excel is to input the project timeline data into a spreadsheet. For the purpose of this tutorial, I’ve created a sample file entitled Sample Gantt Chart Created in Excel with project data that can be downloaded from the Project Management Media Gallery. Additionally, the screenshot below shows the table created for this example. As with any of the other images in this tutorial, you can click on it to see a larger view.

click to enlarge

We will use the stacked bar chart option in Excel to create our Gantt chart. First, as shown in the image below, hold down the CTRL key and select the columns that contain the Task, Start Date, Days Completed, and Days Remaining Data.

click to enlarge

Next, navigate to the Insert screen of Excel and choose the option to insert a stacked bar chart. You’ll end up with an image like that in the following screenshot.

click to enlarge

This isn’t at all what we want as a final version, but we now have a representation that contains our basic information that we can modify to reach our desired appearance.

If you're having trouble getting your initial stacked bar chart to look like the one above, you may need to approach the construction in a slightly different way. First, select the Task and Start Date columns. From the Insert tab on the Excel Ribbon, choose to insert a 2-D Stacked Bar Chart.

Next, right-click on any blank area in the newly created chart and choose Select Data.

click to enlarge

The Select Data Source window will appear as shown in the screenshot below.

click to enlarge

Click on the Add button under Legend Entries (Series). This will cause the Edit Series window to appear.

click to enlarge

We want to add a new series for the Days Completed column. To do this, first click on the button next to the Select Range box under Series name.

click to enlarge

Next, click on the cell containing the name of the column, Days Completed.

click to enlarge

Click on the icon to the right of the box to return to the Edit Series window. Now, click on the button to the right of the box under Series values.

click to enlarge

This time, select the range of cells in the column that contain the actual number of days.

click to enlarge

Click the icon to the right of the data entry area to go back to the main Edit Series window.

click to enlarge

In the background, you should now see the bar representing the series for Days Completed on the chart. Click OK to return to the Select Data Source window.

Repeat the process described above to add another series for Days Remaining. When finished, the chart should look like the following image.

click to enlarge