How to Create a Gantt Chart Template Using Excel 2007 or Excel 2010
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.
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:
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.
Now we create our own table by copying the four columns we are interested in:
- Start Date
- Completed (in days)
- Duration (in days)
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.
- 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)
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.
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:
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
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:
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:
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
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