Excel Project Planning Spreadsheet Version 2

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.

[download id=”14″]

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.

Advertisements
Tagged , , ,

6 thoughts on “Excel Project Planning Spreadsheet Version 2

  1. roddy cheung says:

    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 says:

    Roddy,

    Thanks for downloading the excel file… The downloaded file should be a single xlsx file – not a zip file. Be sure you’re using the following link to download…

    http://www.mlynn.org/wp-content/plugins/download-monitor/download.php?id=14

    Hope this helps… if not – leave another comment and I’ll email you the file directly.

    Regards,
    Mike

  3. Robert says:

    Mike
    You are both right! It is an Excel spreadsheet, but it has been saved with a .zip extension!
    Robert

  4. Robert says:

    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

    • mike says:

      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

  5. Jakie Diggs says:

    Hi Mike,
    I’m not able to open the spreadsheet link. I am receiving a database error. Can you please help me troubleshoot this issue? Thanks!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: