Wednesday, 17 February 2010

Project Plans in Excel (simple, quick and effective)

For any piece of work other than the smallest, it’s worth planning. Planning doesn’t have to mean creating a huge monster in Microsoft Project - I find that Microsoft Excel (or similar) is often sufficient (and a lot more accessible to the team). This post (and the series of posts that follow) describes how to quickly and efficiently create an adequate plan for small to medium sized projects.

I don't expect all developers to be expert project managers, but I do expect my team members to understand the role of the project manager, to know how to work to a plan, and to focus on delivery. And I do expect developers to run their own (small to medium sized) projects from time-to-time.

A project plan can consist of just a list of tasks (preferably with start and end dates) together with the name of the person who will complete the task, but this can be made to communicate a lot more if you can deliver a Gantt Chart too. The name “Gantt Chart” sounds challenging to anybody who hasn’t met one before, but actually it’s rather simple format that most people are familiar with (often without knowing the name). Gantt Charts can contain a lot of detail and embellishment, but I’m going to describe how to create a simple yet communicative chart very quickly.

To illustrate what to do, I’ll use Microsoft Excel 2002, conditional formatting, and formulae.

Let’s start by creating the basic list of tasks, their start dates, and their durations (I’ll ignore the name of the actionee in order to save space):

We can add a formula to calculate the end date: d3 set to =B3+C3-1, etc.

Next, let’s add columns for the days of our Gantt:

To create our Gantt, we want to create solid-coloured cells where the day number (in row 2) lies between or equal to the start and end days respectively. To do this we will use Conditional Formatting. From cell F3 we need the following Conditional Formatting (accessed from the Format menu in Excel 2002):

In addition, we’ve used the Format button to set the pattern for the cell shading to solid green. And, having clicked OK, we see that cell F3 turns green, reflecting the fact that activity #1 is to be performed on day 1.

We now need to copy this formatting to all of the other cells of the Gantt, but if we copy and paste we’ll get the wrong results because the cell references will be move relative to the point where we paste the formatting. So, we need to change the formula to tell Excel which bits of the formula should move when we copy/paste, and which bits should be fixed. The modified formula is: =IF(AND($B3<=F$2,F$2<=$D3),1,0)

Armed with the correct formula, we can use the format painter to copy the formatting, or we can use Copy and then Paste Special. Either way, having done so we will see the end result:

That’s a simple, quick, but communicative Gantt chart. In subsequent posts I’ll describe how to quickly translate day numbers into dates, how to add grouping of tasks, and how to indicate progress (% done).

What I have described is just one of many routes to the same end. There are plenty of resources on the web to guide you to a similar result (or better) with Microsoft software and with others. Plus, SAS/OR offers plenty of project management functionality, including Gantt charts from PROC GANTT.