I'm going to start by removing the groups that we had in the last result - I've never explored how they can successfully be charted. So, let's select the input data area (A2 to F9), go to the Subtotals window, and click the Remove All button (then confirm that you understand that entire rows will be removed). Our chart collapses and looks as shown below.
Let's add a couple of columns that indicate the amount of progress (equivalent to column Q) and the amount remaining. So, insert a couple of columns between Start and Effort. To keep this tutorial simple, the position is important, but you could actually put these columns over to the right if you're familiar with de-selecting data series in Excel charts. Having inserted two columns, let's label them Done and Remain, and let's set their values with formulae. Put D3 =F3*H3/100, and E3 =F3-D3. In other words, Done equals Effort times %Done, and Remain equals Effort minus Done. Copy these formulae down and you'll get the result below.
Now select the area that contains the data to be charted, i.e. B2 to E6, and launch the Chart wizard. In step1 of the Wizard, select chart type of Bar and sub-type of Stacked Bar (see below).
Click Finish. Let's see what we've got:
We can see that the order down the left side is wrong (activity #4 is above activity #3, we'd like activity #1 at the top); if we hide the purple Start bars then we'll have some nice bars that show Done and Remain.
Right-click on the left axis, choose Format Axis, go to the Scale tab and tick Categories in reverse order. See below.
Click OK and you'll see that the order of tasks has reversed. Now right click on any of those purple Start bars, choose Format Data Series, and on the Patterns tab select Border colour of None and Area colour of None. Don't press OK yet. See below.
Then, before you click OK, go to the Data Labels tab and put a tick alongside Category Name. Now you can click OK and you'll see that the purple Start bars have disappeared.
Get rid of the Legend by right clicking on it and selecting Clear. And get rid of the left axis labels by right clicking on one of them and selecting Clear.
It's starting to look like what we want. Right click on one of the activity labels and select Format Data Labels. On the Alignment tab, select Right for Horizontal alignment, and Inside End for Label Position. See below.
Click OK. The labels look neat (although the labels for activity #1 and #3 unavoidably overlap the bars because of the limitations of the left margin).
Right, let's make it look a bit more snappy. Click on the grey background and select Clear; click on the top axis, select Format Axis, and select Lines as None in the Patterns tab; click on the border of the chart and select Format Chart Area, select Round Corners on the Patterns tab, and also select Fill Effects for the Area and then choose one colour and apply a vertical shading style. After having set the fonts to bold, the grid-lines to grey, and bar colours to red and green, here's my result:
They say beauty is in the eye of the beholder, so you may think it looks snappy, or you may not. Either way, it's an eye-catching Gantt chart.
As I said at the top, I find these charts creak under the strain of a larger number of tasks, and they become troublesome to print, hence I prefer using the cells of the worksheet.