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

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.

Find out more about our Excel training London, UK.

Find out more about our Excel training Dorset, Hampshire, Wiltshire.

Find out more about our Excel training Sydney, Australia, please visit

Advertisements

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, Microsoft Excel 2013, Microsoft Excel 2016, 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