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 (15847)

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.

An Excel Project Planning Spreadsheet

Excel Project Management Template

Project planning and progress tracking is an essential part of any successful project, no matter the size. Part of ensuring success is communicating progress with project stakeholders. For small projects, simple meetings will suffice, but anything involving more than a couple of tasks and people typically requires some form of formal project documentation. While Microsoft Project is a great tool, not everyone uses it and while they have a “viewer” version, sometimes it just doesn’t seem worth the hassle. You may want to use a Gantter a free Microsoft Project alternative, sometimes I think it’s just easier to use Excel. While it’s not free – it’s certainly popular, easy to use and offers the ability to export and/or save as PDF files for easy sharing.

Excel Project Plan Example
Excel Project Plan Example

This is why I almost always create a project plan with a Gantt Chart using Microsoft Excel when embarking on a project. In this tutorial, I’ll walk through the steps I took to create a sample Excel-based project planning spreadsheet with a nifty gantt project schedule that shows who’s doing what, when and how complete the tasks are.

Features

I’ve included some important features in this example. Most notably is the Gantt Chart schedule view of the project which automatically provides a view of who is performing what tasks on what date. The example includes logic to skip around weekends and non-working days as well as a separate column to track progress against tasks or goals.

If you’d prefer to skip the article and simply download the excel project planning template, you may skip to page 4 using the page links below to access the download link.
Continue Reading on Next Page

Gantter Project – A Great, Free Way to Manage Projects

I’ve used OpenProj for several years. It’s a nice, free alternative to MS Project. The only problem is sharing the project plans with folks – rarely do people I work with have OpenProj installed. Fortunately, OpenProj gives you the option of saving as a MS Project XML file. This makes it easier but I was still looking for the ability to save the projects in PDF format. This is possible, but only with the paid version of OpenProj. This is when I found the Gantter Project.

Gantter is written to work with Google Apps. It enables you to import MS Project project plans, save them as PDF and even save them directly to Google Docs.

For me, Google Docs and the Gantter Project work best with my custom domain – mlynn.org – but you can use it even if you don’t have a custom domain. Visit Gantter.com to find out more information about this great, free tool to help you manage your projects.