Creating a Gantt Chart using Microsoft Excel – part 2

In a recent blog, I demonstrated how to create a Gantt chart using conditional formatting in Microsoft Excel. In this blog, I demonstrate creating a Gantt chart in Microsoft Excel using a stacked bar chart.

In columns A, B and C, type in the Task Names, Start Dates and anticipated Durations for the tasks within the project plan. As long as durations are at least one day, the End Date in column D can be calculated using the formula = Start Date + Duration – 1.

If you are aware of the relationships between tasks, use formulae to create as many of the Start Dates as possible, based either on previous End Dates or Start Dates, e.g. if you know that the Develop questionnaire task can start the day after the Planning meeting, then the formula in cell B3 is =D2+1.

Select the cells containing the tasks, durations and start date, in our case A1:C13

If using Microsoft Excel 2003 or earlier versions:

Click the Chart Wizard button. The first Chart Wizard dialog box will be displayed.

Choose a stacked bar chart. Click Next.

Click the Series tab. Click Duration, then click Remove.

Click Add. Click on Series 1. Click in the Name box, then click in cell B1. Click in the Values box, then select B2:B13.

Click where it says Series2.  Click in the Name box, then click in cell c1. Click in the Values box, then select C2:C13.

Click in the Category labels box and select from A2:A13.

Click Next. Click on the Legend tab. Get rid of the tick by Show legend, then click Next.

Click Finish.

Position the Gantt chart where you want it on your sheet. Make it large enough to see all the task labels. Right-click on the vertical axis, and click Format – Axis. The Format Axis dialog box will be displayed. Click on the Scale tab, then select Categories in reverse order. Click OK.

Double-click on the Start date series bar to open up the Format Series dialog box. On the Patterns tab, in the Border section, click None and in the Area section, click None. Click OK.

To adjust so that the horizontal axis starts at the Start date and finishes at the End date, copy the Start date of the first task into a blank cell and the End date of the last task into a blank cell. Convert both of these to Number format. Select the horizontal axis, right-click and select Format Axis.

Go to the Scale tab, and type in the start number in Minimum and the end number as Maximum. Click OK. Change Major Unit to 7 (one week). Click OK.

Change font sizes as necessary.

If using Microsoft Excel 2007 or Microsoft Excel 2010:

Click the Insert tab, then in the Charts group, from the Bar dropdown, select the second one in the 2-D Bar section – a stacked bar chart.

On the Chart Tools Design tab, in the Data group, click Select Data. The Select Data Source dialog box will be displayed.

Click the Remove button twice.

Click Add; the Edit Series dialog box will be displayed.

Click in the Series name box, then click in cell B1 (The Start Date heading).

Click in the Series values box, then select the cells comprising the start dates of the tasks, in this case B2:B13. Click OK.

Click Add again; this time click in the Series name box and click in cell C1 (the Duration heading), then click in the Series values box, then select the cells comprising the duration of the project tasks, in this case C2:C13. Click OK.

In the Horizontal (Category Axis) Labels section,click Edit, then in the Axis label range box, select range A2:A13, the task names. Click OK twice.

Go to the Chart Tools Layout tab, then in the Labels group, from the Legends dropdown, select None to turn off the legend.

In the Axes group, from the Axes dropdown, select Primary Vertical Axis – More Primary Vertical Axis Options. The Format Axis dialog box will be displayed. Select Categories in reverse order. Click Close.

Select one of the leftmost bars, which should select all of them, then from the Chart Tools Format tab, from the Shape Fill dropdown, select No Fill. Also from the Shape Outline dropdown, select No Outline.

To adjust so that the horizontal axis starts at the Start date and finishes at the End date, copy the Start date of the first task into a blank cell and the End date of the last task into a blank cell. Convert both of these to Number format. Select the horizontal axis, right-click and select Format Axis.

Type in the start number in Minimum and the end number as Maximum. Click OK. Change Major Unit to 7 (one week). Click Close.

Change font sizes as necessary.

To find out more about our Excel training London, UK, visit http://www.jmdtraining.co.uk/microsoft-office-training/microsoft-excel-training.

To find out more about our Excel training Sydney, Australia, please visit http://www.jmdtraining.com.au/microsoft-office-training/microsoft-excel-training

About jdonbavand

I am a trainer of Microsoft Office, Microsoft Project and Crystal Reports. I have called my blog "If Only I'd Known That...." because I hear it so many times in training sessions. In fact, if only I had a £100 (or 150 Aussie dollars)for every time someone says "If only I'd known that." ....
This entry was posted in Microsoft Excel, Microsoft Excel 2003, Microsoft Excel 2007, Microsoft Excel 2010, Uncategorized and tagged , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s