Project Management with Microsoft Excel

Excel Project Management Template

“If you fail to plan, you are planning to fail!” —Benjamin Franklin

Among the many titles in Ben Franklin’s vast career, I’m fairly certain you will not find “Project Manager.” However, you can be sure that he understood the value of planning to ensure the success of any activity requiring multiple tasks.

Benjamin Franklin didn’t have the benefit of computers and couldn’t leverage Microsoft Excel or any tools to aid in organizing his projects… But I bet he would if he was able. So, in celebration of the fact that we DO have access to these tools, in this article, I’ll provide some information about Project Management and more specifically about Microsoft Excel as a tool to help you manage your projects more effectively.

What is ‘Project Management’?

Project management is the discipline of planning, organizing, motivating, and controlling resources to achieve specific goals. A project is a temporary endeavor with a defined beginning and end (usually time-constrained, and often constrained by funding or deliverables), undertaken to meet unique goals and objectives, typically to bring about beneficial change or added value. The temporary nature of projects stands in contrast with business as usual (or operations), which are repetitive, permanent, or semi-permanent functional activities to produce products or services. In practice, the management of these two systems is often quite different, and as such requires the development of distinct technical skills and management strategies.
The primary challenge of project management is to achieve all of the project goals and objectives while honoring the preconceived constraints. The primary constraints are scope, time, quality and budget. The secondary —and more ambitious— challenge is to optimize the allocation of necessary inputs and integrate them to meet pre-defined objectives.

What is Microsoft Excel?

Microsoft Excel is a commercial spreadsheet application written and distributed by Microsoft for Microsoft Windows and Mac OS X. It features calculation, graphing tools, pivot tables, and a macro programming language called Visual Basic for Applications. It has been a very widely applied spreadsheet for these platforms, especially since version 5 in 1993, and it has almost completely replaced Lotus 1-2-3 as the industry standard for spreadsheets. Excel forms part of Microsoft Office. The current versions are 2010 for Microsoft Windows and 2011 for Mac OS X.

Cells, Formulas and other Excel Content

Excel is an intuitive and easy to use tool for creating anything from simple todo lists to math intensive spreadsheets. It’s power is in its extensibility. Excel documents are called spreadsheets. Spreadsheets are made up of cells. Cells are arranged in a tabular format of rows and columns. Each cell in a spreadsheet can contain formulas or plain text. There are approximately 350 individual formulas that exist in current versions of Microsoft Excel and these can help you perform calculations and data manipulation. A simple formula can be seen in the example below:

Click on the next page to continue reading and for more information about our example formula… or skip to page 3 to check out the free example download.

Excel Project Planning Spreadsheet Updated Version 3

*** Updated 11/2014 ***

Be sure to check out the updated version of this template.

This article builds on a previous post which covers Project Planning leveraging Microsoft Excel

Some users of the Microsoft Excel Project Planning Spreadsheet Template expressed trouble modifying the date ranges when customizing the template. To make this process a bit easier, I’ve updated the project planning template to automatically show the day of the week in cell H3 based on the date value entered in cell H2. So to be clear, the process of customizing this spreadsheet should begin with modifying the date value in cell H2. Once you’ve entered the first date you want to show in the gantt view in cell H2, simply click and drag to expand the dates horizontally in row 2. Continue reading “Excel Project Planning Spreadsheet Updated Version 3”

Excel Project Planning Spreadsheet Version 2

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


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.