Pin Me
notion

Using Excel to Create a Gantt Chart

written by: Michele McDonough • edited by: Michele McDonough • updated: 9/13/2012

Although Excel 2007 doesn't have a built-in Gantt chart wizard like the ones found in other software products, it's still possible to create this useful project management tool within the spreadsheet application.

  • slide 1 of 13

    Excel Gantt Charts

    Sample Gantt Chart in Excel The Gantt chart is a very helpful and popular tool in the field of project management. A Gantt chart is just one of many project management forms used throughout the project life cycle.

    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.

  • slide 2 of 13

    Create a Table with Project Data

    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.

    Table for Gantt Chart Data 

  • slide 3 of 13

    Create a Stacked Bar Chart

    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.

    Highlight Data for Chart 

    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.

    Initial Gantt Chart 

    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.

  • slide 4 of 13

    Alternative Directions for Creating the Initial Stacked Bar Chart

    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.

  • slide 5 of 13

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

    Select Data 

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

    Select Data Source Window 

  • slide 6 of 13

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

    Edit Series Box 

    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.

    Select Range 

  • slide 7 of 13

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

    Name Range 

    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.

    Series Values 

  • slide 8 of 13

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

    Select Values for Series 

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

    Main Edit Series Window 

  • slide 9 of 13

    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.

    Initial Chart 

  • slide 10 of 13

    Modify the Stacked Bar Chart

    Now that we have the framework in place, we need to make a few more modifications so that the final result takes on the standard appearance of a Gantt chart.

    First we want to make sure that the tasks on the chart are listed in chronological order from oldest to newest. To do this, right-click over any of the task names and choose Format Axis from the dialog box. Check the box in front of the option “Categories in reverse order” and then close the window. This process is shown below.

  • slide 11 of 13

    Format Axis to Sort Tasks 

    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.

    Remove Start Date 

    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.

    Find Date 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.

    Adjust Axis Options for Dates 

    Finally, we arrive at something that looks like a Gantt chart.

    Final 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.

  • slide 12 of 13

    Additional Resources

    Be sure to take a look at the other Excel project management tutorials found here at Bright Hub. Among the items included in this collection are articles on how to create a Six Sigma histogram and how to construct a fishbone diagram. New and updated articles are added on a regular basis so bookmark us and check back often.

    If you're looking for more sample forms and downloadable templates, check out Bright Hub's resource guide Over 50 Free Project Management Templates and Sample Forms.

  • slide 13 of 13

    References & Image Credits

    Microsoft Excel Official Site, http://office.microsoft.com/en-us/excel/

    Screenshots taken by author.

Working with Gantt Charts

We have several articles related to creating and working with Gantt Charts. Click each for some helpful information.
  1. What Is a Gantt Chart?
  2. Gantt Chart Examples and Tutorials
  3. Using Excel to Create a Gantt Chart
  4. How to Make a Gantt Chart in PowerPoint
  5. Project 2007: How to Create a Gantt Chart