Save content
Have you found this content useful? Use the button above to save it to your profile.
Excel
iStock_G0d4ather_excel

How to create an Excel Gantt chart

by
18th Sep 2015
Save content
Have you found this content useful? Use the button above to save it to your profile.

Anyone who has ever wanted to manage complex projects will be aware of Gantt charts. But did you know you can construct them in Excel? Here’s a tutorial that shows you how.

We have Excel blogger Amiq Khan to thank for bringing this technique to our attention last month. His approach pushes Excel into territory that is usually occupied by Microsoft Project and other specialist tools. But if you don’t want to spend the money or time getting to grips with them, Khan demonstrates that you can construct a graphical project plan without leaving the comforting confines of your favourite spreadsheet program.

Since Henry Gantt developed his namesake tool in 1910s, the horizontal bar chart has become the accepted method to present a visual summary of a project schedule. If the Gantt chart was good enough for the Hoover Dam, it should do the job for you.

A Gantt chart can help you spot any gaps in your work load, see the expected length of the tasks and anticipate any overlaps in your schedule.

The Gantt chart takes the format of rows of bars representing an activity, with the length determining the projected time scale. To the left of the chart you list the tasks you wish to schedule, and then along the horizontal axis you list the time scale.  

Although Excel doesn’t have a readymade option to turn project data intp a Gantt chart, building one for yourself is fairly straightforward.

The data needs to be organised into three columns: one showing the tasks to be scheduled; then start dates for the tasks; and their expected duration. Here’s what our sample data looks like:

Brainstoming

01/09/2015

2

Research

03/09/2015

3

planning

06/09/2015

3

interviews

07/09/2015

4

implementing

09/09/2015

4

Because Excel was not designed from the ground up to facilitate producing this type of chart, the Gantt exercise requires quite a lot of tinkering with the data and Format Chart Area options to match scheduled tasks to the correct duration data, and to format the chart axes to present the date information in a meaningful way. Here is a step-by-step summary that explains how.

Starting from this data, select bar chart from the Ribbon menu and choose a 2D bar chart:

At this point the data on the vertical axis is represented by numbers:

Starting a Gantt chart layout in Excel 2010

You can assign the names of their tasks to your bars by right clicking on the data range and choosing ‘Select data'. The data source window, which you used to insert the start date and duration, will appear:

Gnatt chart

If you click on Start date under the Legend entries series, and then click on Edit in the right hand box, the axis labels window appears:

Gantt chart

 If you highlight the scheduled tasks in the first column of your spreadsheet, the tasks will appear on your developing Gantt chart.

You will quickly notice that the tasks are in reverse order. Don’t worry, you can allocate the tasks to their correct bar by right clicking again on the vertical axis and click on Format Chart Area. A format axis window will emerge. You will be confronted with a list of axis options, but the one that we are interested in is checking the box which says “Categories in reverse order”. Your scheduled tasks will now align with the duration data:

gnatt-chart

Your chart is looking more like Gantt variety except for those pesky blue bars. But the blue bars are fairly easy to hide. All you have to do is right click on the blue bars and then choose ‘Format Data Series’.

gantt-chart

On the Format Data Series box select fill which you will find on the left hand side under Fill options click on No Fill and then under border line select non line. This will have the effect of rendering your blue bars invisible. You will now see that your chart much more resembles a Gantt chart:  

gantt-chart

You’re nearly done, but we just have a few things left to do to tidy up your chart. You will notice that there is a huge empty space at the beginning of your chart. Since this space offers no information, we can shift your bars over to the left:

gantt-chart

The final step in the sequence is to select the first date on your scheduled tasks in the data table, right click, and select format cells. Under the General category, you will see a five-digit number. This number represents Excel’s internal formula for the first date on your scheduled tasks (explanation here). Highlight the number and press Ctrl+C to save it to your pasteboard. Now close this window, because we will make the change in the Axis Options window instead.

Now click on the dates along the top of your chart, right-click and select the Format Axis option at the bottom of the floating menu. Change the first “Minimum” option from auto to fixed and paste in or type your starting date value. You can also increase the unit number of your axis to enlarge the spaces between each date:

gantt-chart

When you click OK, you should now have achieved the characteristic Gantt chart look:  

gantt-chart

If you've ever used Excel for project management and produced charts in this way, or have an experiment with this tutorial, let us know whether you think Excel is up to the task. Or is there a better, more efficient way?
Tags:

Replies (0)

Please login or register to join the discussion.

There are currently no replies, be the first to post a reply.