Excel Project Planning Spreadsheet Version 2

*** Update September 20, 2012 *** I have released an updated version which you will find detailed here:

http://www.mlynn.org/2012/09/excel-project-planning-spreadsheet-updated-version-3/

 

The Excel Project Planning spreadsheet has been a handy tool for many of the projects I’ve worked on and I’ve continued to tweak and add features it. Therefore, I thought I’d add this post to talk about some of the enhancements and provide a link to the updated version.

Enhancements from Version 1 to Version 2

Task color coding based on progress against current day

As you can see from the attached image, the Gantt cells are now color coded.

A – Green cells indicate work that was scheduled and has been completed (100%) as indicated in column F.

B – Yellow cells indicate that tasks were scheduled and more than 50% of the task has been completed but the start date is now in the past.

C – Red. Cells are filled with Red when tasks are scheduled to have had work completed but have not yet started and the start date has come and gone.

D – Dark Blue.  As in version 1 of the Excel Project Planning Spreadsheet, cells filled with dark blue indicate work is scheduled to be started and completed on these dates.

Today’s Date – What/If Analysis

I’ve added a field and some conditional formatting noted in the above image by label E – which enable you to analyze the project as if today’s date were progressing beyond the actual date/time when you’re viewing the project planning spreadsheet.

Clicking the spinner in cell C29 – either up or down will increment the value of Today’s Date.  This will cause the conditional formatting in the Gantt area to show you what the project color coding will look like based on current progress on the date provided in cell B29.

This will also highlight another enhancement that I made to make progress against the current date a bit more obvious.  You’ll notice the dates that appear at the top of the Gantt area that are filled with the project dates (eg: 5/19).  These will now appear in a Grey filled font as they drift into the past.

All of these new enhancements have been accomplished using conditional formatting combined with functions.  To view these, select the cells where the formatting appears and from the home tab, click conditional formatting, then manage rules.

Here’s a link to the spreadsheet download.

Excel Project Plan v3 (15775)

I hope you enjoy the new features.  Please Tweet, Like or G+ if so!  Also, if you have a feature request, feel free to comment or use the contact page to reach me.

11 Replies to “Excel Project Planning Spreadsheet Version 2”

  1. i download your spreadsheet v2, and there are files inside the zip file but none of them are Excel file.

    Pls let me know how to use your spreadsheet or simply just an Excel file be more straight forward

    regards

    roddy

  2. Mike
    Sorry, I must be missing something. I am not getting any of the bars in the Gantt area. I looked at teh conditional formatting and can only see the formats to distinguish between dates during/after the project trimeframe.
    Could if be taht I am in the UK and dates are in the format dd/mm/yyyy?
    Regards
    Robert

    PS – I get a lot of Stack Overflow messages when accessing your website using IE8

    1. Thanks for the feedback regarding IE8 – I have not tested using this version but will have a look. Regarding the gantt – I will do some testing and get back to you… its very likely due to the date format differences.

      Regards,
      Mike

  3. Hi, thank you for your template, exactly what I was looking for.

    I am a bit stuck on changing the dates from May to now, September. I have amended the todays date to today manually but none of the dates in the project gantt charts changed. When I change the first one manually and expand the selection, they all display as if they were weekends. Am I doing something wrong?

    Renata

  4. Hi Mike,
    This spreadsheet is fantastic, thank you so much for this. It helps so much with my work. I just wonder if there is a way to customise this so that it highlights the work of each person in different colour. For what I am using it for (to schedule and allocate people’s work), I don’t need to colour code the progress of the projects but rather the allocation of everyone’s work. Hope you could helps.
    Regards,
    Terri

Leave a Reply

Your email address will not be published. Required fields are marked *