Project Management with Excel - Template for Project Plan

This article has been originally published on www.LinkedPHPers.org

While diving into various project management internet sources I found plenty of templates for creating project plans in Excel. Most of them are not very sophisticated, delivering a simple table in which you can simply put the list of tasks (sometimes even on several levels - but that's already the top of sophistication I've seen). I have to honestly admit I was reviewing only free templates - so it might be that the paid ones are delivering some more functionality.

However since I am not a great fan of MS Project, while I have to in my daily work use some project plans - during several years I have created a plan template in Excel and decided to share it with you - hopefully you will like it.

It is maybe not as sophisticated as MS Project, doesn't take care of resource management at requires some Excel knowledge to be used but it fits the purpose for me - so hopefully it will also for you.

Now I actually could simply give you the link to the file and let you download it and play around - but I think it would be better if I will also write several guidelines and information on how I use this template.

So - here is the LINK :)

And below some explanations, guidelines or best practices I use to make this excel effective.

The example

Under the link you actually do not have clean template - there is an example of project plan (or actually a part of it - since the CRM Implementation will most likely take more phases than we can see there) for some simple project.

And here is the very first thing - to avoid plenty of comments from Agile believers and evangelists - this project plan is designed to be used in waterfall or waterfall-based formal procedures. It does not fit to pure Agile projects, is not intended to be used in such projects, hence it most likely be not effective for that. Full-stop - please do not comment the plan does not fit SCRUM :)

So, once clarified that, I would like also to mention for the record that this plan is not coming from any project I've been managing or running. It has been created in less than 30 minutes just for the example purpose and using various techniques I use in this project plan. So please do not comment in scope of te timings (e.g. how the development can take 5 days, and testing another 5) - as this is not the case in here :)

Data Structure

Multi-level tasks

You can have tasks in different levels. In an example attached you have overall 5 levels - including the highest which project itself - CRM Implementation in cell B3 - actually referring to the plan title specified in A1 cell.

You can add more levels by adding more columns there - but I have to admit I never had to do so and these 5 levels were a good fit for most of my projects I used this template for.

You can play around levelling your tasks in any way you want - there are no strict rules in there. Actually the whole Excel does not implement strict rules - I do not add any cell protection, since I already learned sometimes I need to do things in there I was not doing anytime before, so it is easier for me if I do not limit that. Of course when I send this to someone - sometimes I add read-only passwords, but only for review purposes (which actually can also be done using e.g. SharePoint rights).

So - having in mind no limitations, you can have several first-level tasks/projects. And I actually been using them on programme management assignments - where I had several connected projects, which were specified actually in single programme plan.

The second level I use mostly for workstreams. In the example the workstreams specified there are sequential - but I've been using this spreadsheet in projects where the workstreams were parallel and worked fine for me.

The rest of the levels are very specific to the tasks itself. As you can see there are tasks that appears only on third level, there are ones that are levelled down. It is very much dependent on the project.

Creating tasks

To create task you simple add a row - or better insert it, since once inserting Excel will automatically use the upper-row formatting - so you will not have to copy conditional formatting that is implemented. You will also need to copy different formulas if you want to use them - this mainly refers to:

• formulas specified in column A - a simple one that is actually creating the number of the task basing on number of row
• "grayed" strings created in columns B:E - these are formulas that are creating in column B the unique string, kind of a task identifier. The string is created by adding all the level names together with the task name. In fact in this spreadsheet this is not something you very much need, so you can actually skip this. The idea of this column (and it's use case) was when I was adding another sheet in here that was to track the resource availability and check whether recource is available for specific task. Since I did not wanted to copy (or refer) to another spreadsheet the whole plan - I was rather using B column as source for the list of all tasks and selecting task from the list to check whether task timing matches to resource availability. So bottom point - if you don't want to use it for you own purposes, you can remove the formulas and it will not affect the sheet itself.
• Formulas in columns G:L - they are connected with different timings or statuses, which will be described later. In general the formatting (especially the conditional formatting in column H) should be replicated if you insert the task, while other formulas are something you need to decide whether you want to copy them or do them in a way that will fit your purposes.
• Gant-chart in columns M:AY - here you have pure conditional formatting that is coloring the cells accordingly to timings and status. This should be replicated if you insert the rows, if you add them - you might need to copy the formatting by yourself.

Task owners

This is actually very simple column G, where you put the task owner in pure text. However I found it quite frequently used by various team members, that are basically filtering by this column to see what tasks they should be working on, or possibly forgot to update their status.

I have to admit that I had developed some additional sheets that are also adding some resource management functionalities in here - such as connection to out-of-office calendars or analysis on the workload - but this is not the part of this spreadsheet... Maybe I will share that ones as well if you like this one :)

Progress

Again quite straight forward column H. You can add there a percentage of task completion. The column will change colors depending on whether the task is completed (100%), in progress (more than 0% and less than 100%) or not started (0%). The coloring is however more sophisticated, since it refers mostly to task status. Full color description you can find there...

There is additional things I use here frequently which as AVG() formula. I use it for tasks that has some child-level tasks. In that situation I do not add here simply the percentage, but rather calculate it using AVG() formula for the child tasks - you can see examples in cells H5, H18 or H41 (and actually quite many more :)). The most "advanced" (or ranged) example is actually H3 - where you count the average of all progresses.

Task timings - Start, Duration (workdays). Finish

Here you can play actually most. The easiest way is just to type in dates - but I almost never do it so easily :)

In most cases I reference Start (column I) dates depending on other tasks or even workstreams. This enables me to automatically shift the dates if I update some of the crucial, critical path tasks. So on the start dates you can find following ways to specify them:

• references to other end dates (in most cases),
• MIN() formulas for tasks that has children - easiest way to get the start of the workstream or complex task (e.g. I4, I8 etc.)
• MAX() formulas if you want the task to start after some set of other tasks is finished (e.g. I46) - you can use also here references, all depends on your needs :)

The Duration (column J) is also used in different ways:

• by clearly specifying number of working days task will take (it is very important you use working days, but according to standard Excel working days calendar - since it will be later on used to calculate the end date) - e.g. J6, J7
• by calculating number of working days using NETWORK.DAYS() formula border dates - e.g. J3, J5, J18

The Finish (column K) date can be used in following ways:

• by clearly specifying the end date - very rarely, or actually almost not used by myself :)
• by using MAX() formula to determine end date basing on child tasks end dates - e.g. K3, K5, K24
• by using NETWORK.DAYS() formula to calculate the end date basing on a start date and number of working days specified in J column - e.g. K6, K7

As per Finish column there's additional, simple functionality there which turns this column into Red once the date specified there is earlier than Start date - so that's kind of error notification.

There is one important thing to mention in here - the start/duration/finish dates are not considered as non-stop work for the task. Therefore if you sum up the durations - you won't get the price, but only a time in which project should be finished. These times are more time slots within the task needs to be completed - therefore there's not problem in having several tasks assigned to same person at the same timeslot - since that person can work on them in parallel or sequentially within his/her own workplan.

Status

Status (column L) is always calculated basing on the task progress and start/end dates referring to current date. The value of this column is used for conditional formatting of Progress column, as well as same formulas are used in Gantt Chart. Basically the rules for Status are following:

• Inactive - the task has been completed (Progress = 100%) and time for completion is in the past (has already passed) - color for that is Grey
• Delayed - the task is not completed (Progress < 100%) and time for the completion is in the past (has already passed) - color for that is Red
• Dangered - the task is not completed, but it has started and not reached 50% while the time assigned to this task has passed 50% of overall time - color for that is Orange
• On Track - the task is not completed, but it has started and should be finished on time - color for that is Green
• Not Started - the task has not started (Status = 0%) while the currently we are within start-finish period and task is not Endangered - color for that is Light Blue
• Planned - the tas has not started (Status = 0%), but the start date is in future

I know it might look a bit complicated - but after a while of working in here it is quite easily to get used to these colors and statuses and no longer think on formulas that are calculating them.

Gantt Chart

Gantt chart in the template is created automatically - using the conditional formatting formulas that bases on current date, task dates, row 2 in which you have the calendar-week days (wee commencing specific date) and task status. The coloring is created automatically - so there's not problem in adding some texts or notes in the Gantt chart area - it should not break as long as you will not start merging or dividing cells.

You can also format the cells (including coloring them) in the way you won't, but you need to mind the fact the conditional formatting will override your formatting - you can see that on rows 4 or 23 where the user formatting of making the cells ligh-blue is overridden in some of the cells by conditional formatting dependent on task specifics and status.

In scope of date frames - in row number 2 you can notice the dates. The only date you should actually set to get the timeframe of your project is date specified in M2 cell. The rest of the dates are calculated automatically. It is also important that you put there Monday date - since the formulas are designed in a way that all the dates in the row 2 are based on a "week commencing specific Monday".

You can of course extend the number of weeks presented on the Excel - the easiest way to do so is actually copying the last column of the sheet (in an example AY) into next columns - that way both formulas for calculating the date, as well as conditional formatting should be copied correctly and should work fine.

I hope you will like this template. Please do not hesitate to let me know what do you thin in comments. As I mentioned - it works for me, and I would really love to know whether it will work for you as well.