Let’s begin by adding a group column (inserted as column A, and then filled with appropriate values):
Select cells A2 to E6, i.e. the cells that have the column headers and our data. Launch the Subtotals window (in Excel 2002 it’s available from the Data menu). Use ticks to add subtotals to Start, Effort and End (in the summary rows we want to see summarised values for these three columns - yes, I know it's not appropriate to "sum" Start and End, just bear with me!):
When you click OK you’ll see that outlines have been added, and Start, Effort and End are being totalled (and sub-totalled). We need to change the “(sub-)totals” for start and end to show minimum and maximum instead (see, I asked you to bear with me!). Select column C and use Edit/Replace to replace “SUBTOTAL(9,” with “SUBTOTAL(5,”; and in column E, replace “SUBTOTAL(9,” with “SUBTOTAL(4,”. Finally, you might want to select column A and remove the word Total. The result looks like this:
Q: What was that stuff with changing subtotal 9 to subtotal 5 and 4?
A: Excel's subtotal function does more than subtotaling. The first parameter is a numeric code that tells the function what you want it to do. 9 indicates summarisation, i.e. adding-up, but 5 indicates you want to see the minimum value, and 4 indicates maximum value.
To finish our work we might a) Replace “Grand” in cell A9 with something more suitable such as “TOTAL”, and b) enhance our conditional formatting to make the (sub-)total rows look distinctive. We can spot them because the task name is blank in these rows. Thus we change the conditional formatting as shown below for cell G3 as an example (then just use Format Painter to spread it across the whole Gantt chart). Note the additional clause in the AND function of the first clause in addition to the extra condition:
And here’s the final result:
This series of three posts has shown how to quickly and easily create a simple Gantt chart. My objective has been to produce a neat, communicative chart without too much effort. In addition, the chart has to be robust and allow me to make changes (such as adding tasks or changing them) without disturbing the chart and requiring me to start from scratch. These charts demonstrate that the developer is in control of their project (without spending more time on planning than on delivering the goods).
If I'm working on a large enough project I sometimes add a couple of embellishments to the chart (given that the size of the project merits the extra effort involved). I like to highlight "today" and I like to track %done and show it on the pseudo-bars of the Gantt chart. I'll describe how to do this in a bonus post next week.
It is possible to use Excel charts to create a similar output. The chart can look more pretty than what we've achieved above, but I find that it can become unwieldy and difficult to read & print as the number of tasks grows. Hence, I choose to use the worksheet rather than a chart. I'll describe how to do this in a bonus post in a subsequent week.
