ComputersConsumer ElectronicsCell PhonesHome Theater & AudioGraphic Design & Video EditingInternetIndustrial Technology

How to Create a Gantt Chart Template Using Excel 2007 or Excel 2010

Updated on May 1, 2016

How to Create and Use a Gantt Chart, a Powerful Project Management Tool

A Gantt chart is a popular Project Management tool which is used to show the progress of the tasks or events that make up the overall project. It consists of a list of all tasks on the vertical axis and the time the project will take on the horizontal axis. Gantt charts:

  • Allow Project Managers to easily manage the overall projects status
  • Assist you in planning and scheduling the tasks that make up the overall project
  • List all the tasks that make up the project
  • Show when each task begins and ends and how long it will take
  • Indicate the start and end of the overall project

Often Gantt charts are created using MS Project (specialist software for Project Managers created by Microsoft). However, many companies restrict access to Project as it has a separate license, so today we will look at creating a Gantt chart in Excel.

In my example that we will work through today, I am going to create a Gantt chart to illustrate the progress of my project which is to rollout Office 2007 to 555 users across seven offices in Asia Pacific.

Example of a Gantt chart created using Excel 2007 or Excel 2010.
Example of a Gantt chart created using Excel 2007 or Excel 2010. | Source

Creating a Gantt Chart Using Excel 2007 or Excel 2010

To begin, we start with data in a table which I have illustrated below for the example we will work through:

Table containing the data the Gantt chart will be created from in Excel 2007 or Excel 2010.
Table containing the data the Gantt chart will be created from in Excel 2007 or Excel 2010. | Source

First, I need to create another table that contains the data that we will need to create the chart. We need the Country and Start date from the existing table and two new columns Completed (in days) and Duration (in days).

To create this new table, we need to use the following formulas to create the data.

  • First, we calculate Completion which is the Number of Users Migrated divided by the Number of Users
  • Next, we calculate Duration (in days). This is Completion Date minus Start Date
  • Finally, Completed (in days) is Duration (in days) multiplied by Completion

Let’s use Hong Kong to explain this with an example:

  • Start Date: 15/01/2013 (Cell C46)
  • Completion Date: 31/12/2013 (Cell D46)
  • Number of Users: 150 (Cell E46)
  • Number of Users Migrated: 25 (Cell F46)

Completion is =F46/B46

Duration (in Days) =$D$46-$C$46

Completed (in Days) = $J$46*$H$46

Note: The $ in the formulas tells Excel to use Absolute references so that when we copy the formulas to a new table they do not change.

Additional columns created using the above formulas in Excel 2007 or Excel 2010.
Additional columns created using the above formulas in Excel 2007 or Excel 2010. | Source

Now we create our own table by copying the four columns we are interested in:

  • Country
  • Start Date
  • Completed (in days)
  • Duration (in days)

Table created from the initial table to be used in the creation of our Gantt chart in Excel 2007 and Excel 2010.
Table created from the initial table to be used in the creation of our Gantt chart in Excel 2007 and Excel 2010. | Source

Now, we need to begin to create the chart itself:

  • Select the table we just created
  • Click the Other Charts button on the Insert tab in the Charts Group
  • Choose All Chart Types
  • Next, click on Bar and then Stacked Bar, then OK

We now have our first chart, which you can see below requires some tidying up.

Stacked chart created using Excel 2007 or Excel 2010.
Stacked chart created using Excel 2007 or Excel 2010. | Source
  • Select the chart
  • On the Design tab, click the Select Data button in the Data group
  • First, change the Horizontal (Category) Axis Labels and select only the Country titles (A110 – A116)
  • Add Start Date to the Legend Entries (Series)

Editing the Series to tidy up the initial chart created in Excel 2007 or Excel 2010.
Editing the Series to tidy up the initial chart created in Excel 2007 or Excel 2010. | Source

The Next Step is Critical!

Select Start Date in Legend Entries (Series) and click the little blue up arrow until it is at the top.

Correctly ordering the Series in the initial chart in Excel 2007 or Excel 2010.
Correctly ordering the Series in the initial chart in Excel 2007 or Excel 2010. | Source

Now, we need to tidy up the horizontal axis as it currently a mess:

This part is a little complicated, so bear with me while I explain how Microsoft works with dates.

When you enter in a date, Excel does not store the date as a date, it stores it as a number. This number is the number of days since the 1st of January 1900. This allows Excel to work with dates stored in UK and US format (day / month or month / day). So the 1st of May 2013 is stored by Excel as 4,395.

We need to start our chart on the 15th of January 2013 and end it on the 31st of December 2013. First we need to get Excel to tell us the numbers that correspond to those dates. To get these:

  • Select the column containing the dates (Start Date and Completion Date)
  • Right click and choose Format Cells
  • Change them from Date to Number
  • Note down the biggest and smallest numbers (my numbers range from 41,289 to 41,639)
  • Click Undo to turn the dates back into dates
  • Click on the chart
  • Select the Vertical Axis
  • Right click and go to Format Axis
  • Now we need to update the Axis Options
  • The Minimum is the number that corresponds to the start date of your project (41,289 in my example). Click Fixed and enter in the number
  • The Maximum is equal to the end date (which is 41,639 in this example). Again select Fixed and enter in the number
  • Major Unit is 60 which represents the number of days in two months.
  • Finally, Minor Unit is 1 which equals one day

Note: When changing Axis Options, work from the bottom to the top, otherwise Excel will reject your changes

Your screen should look similar to mine:

Changing the numbering of the horizontal axis in our initial chart in Excel 2007 or Excel 2010.
Changing the numbering of the horizontal axis in our initial chart in Excel 2007 or Excel 2010. | Source
The chart showing the new horizontal axis numbering in Excel 2007 and Excel 2010.
The chart showing the new horizontal axis numbering in Excel 2007 and Excel 2010. | Source

Next, we need to hide the Start Date data to transform the chart into a Gantt chart. To achieve this:

  • Select the Start Date data series in the chart itself
  • Right click and navigate to Format Data Series
  • On the Fill tab, select No Fill
  • Next, on the Border Color tab, choose No Line
  • Click Close and return to the chart
  • Now, select the Start Date legend item and delete it

Voila, the Start Date data is no longer visible in our Gantt chart

Our stacked bar chart now becomes a Gantt chart in Excel 2007 or Excel 2010.
Our stacked bar chart now becomes a Gantt chart in Excel 2007 or Excel 2010. | Source

Now we need to complete the final tidy up before our chart will be ready for public consumption:

  • Next, we need to get rid of those annoying vertical grid lines. Select them and hit the Delete key.
  • Click Close

Your chart will now look something like this:

Gantt chart now without grid lines in Excel 2007 or Excel 2010.
Gantt chart now without grid lines in Excel 2007 or Excel 2010. | Source

Finally, to complete the chart, we need to add a Title. I also choose a Chart Layout that I think makes the Gantt chart much more informative.

  • Select the chart
  • Click the Design tab and then select Layout 5 from the Chart Layouts group
  • Click on Chart Title and change it to something more informative (I chose Office 2007 Roll out Project - Current Status by Office)
  • I also like a 3D effect so I changed the Chart Type to Stacked Horizontal Cylinder
  • Then I right clicked and chose 3-D Rotation and changed both X: and Y: to zero

I also changed the Completed in Days and Duration in Days colours to green and a nice bright red by:

  • Selecting the Completed in Days Series, right click and then click the Fill tab
  • Choose Solid fill and select green and click Close
  • Repeat for Duration in Days and use red rather than green

The Gantt chart is completed; it should look something like mine below:

Completed Gantt chart, created from a stacked bar chart in Excel 2007 or Excel 2010.
Completed Gantt chart, created from a stacked bar chart in Excel 2007 or Excel 2010. | Source

Using the Completed Gantt chart as a Template

Now that we have created and configured our Gantt chart, it can be used as a template to create new Gantt charts. To do this:

  • Take the table we created as the basis of the Gantt chart (example shown below) and change the data to reflect your new project and all its tasks
  • You can either take away or add to the list of tasks by adding lines within the table. To illustrate how to do this, if I add a New Zealand to row 117 in my table below, Excel will not add it to our chart. If I add a new row between rows 114 and 115 and add the data for New Zealand it is added to the chart successfully
  • Start dates, completion dates and durations can be similarly changed to reflect the new project
  • Changes to your table will be immediately reflected in your Gantt chart
  • Both the table and the chart can be copied to new worksheets to create multiple Gantt charts

Table that can be used as part of a Gantt chart template in Excel 2007 and 2010.
Table that can be used as part of a Gantt chart template in Excel 2007 and 2010. | Source

Conclusion

A Gantt chart is an extremely popular and powerful Project Management tool that allows you to visually display the progress of a project in Excel. Each task within a project is also tracked and its progress charted. In today’s hub:

  • We created two tables, the first contained the projects current status and the second created to build the chart itself
  • Finally, we built and configured the chart itself

Along the way, we learnt that Excel stores dates as numbers and how to convert dates to numbers and back again. We also experimented with Chart Layouts and 3-D effects.

I hope that you have enjoyed reading this hub and have found it useful and informative. As always, creating something new provides you with insight and further understanding of Excel and I hope that you have learnt as much as I did along the way. Please feel free to leave any comments you may have below.

© 2013 Robbie C Wilson

Comments

    0 of 8192 characters used
    Post Comment

    • profile image
      Author

      Robbie C Wilson 2 years ago

      Thanks for your kind comment, so happy that you found it useful :)

    • strategylab profile image

      Jeph Maystruck 2 years ago from Regina, SK

      Very useful! Thanks so much!!

    • profile image
      Author

      Robbie C Wilson 3 years ago

      Hi Thief12,

      Thanks for your kind comment. Glad you found it useful. Getting access to Project can be tough as it has a separate licence.

    • Thief12 profile image

      Thief12 3 years ago from Puerto Rico

      Useful tips for anyone who doesn't have access to Project. Voted Up and Useful.