Category Archives: Excel

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.

Tagged , , ,

An Excel Project Planning Spreadsheet

* Be sure to check out the updated version with color coding and other enhancements!

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 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 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.

To start, I’ve taken a screenshot of my example and I’ve broken it down into the basic sections.

The image below shows the various sections needed to create a basic Excel Project Planning Spreadsheet. Each section is labeled A through F.

Excel Project Planning Example

Excel Project Planning Example

Section A – The What

Column A contains a simple list of the tasks you’ll be looking to achieve.  Start simple but try to get this as complete as possible before moving on to the other sections of the plan.

Section B – The Who

Column B in our project planning spreadsheet contains ‘the who’.  Who will be performing or responsible for the tasks in column A?  This can be a comma separated list but keep all references to a single cell.  One thing to consider is whether or not you want to format the cells to wrap – or simply expand the columns so all of the values can be seen.  If you choose to wrap the cell’s contents, note that the schedule section (F) will contain irregular height’s.

Section C – The When

Columns C and D contain the start and the end dates for when the who will complete the what.

Section D – The Length

Column E is our first computed value and will display the working days between the start in column C and the end of the task in column D.  We use the Excel function networkingdays() to compute this value.

=NETWORKDAYS($C4,$D4)

Section E – Progress (In Percent)

Showing progress is a critical part of communication during any project.  Using Excel’s Conditional Formatting, we can get pretty flashy.

The image below shows the sequence of steps to follow in order to conditionally format column F as data bars representing the total percentage complete for the task in column A.  You’ll want to make sure you select the cells in column F and format them as %, then follow the steps in the image below.

Step 1. Select the Cells in Column F that will be formatted.

Step 2. Make sure you have the “Home” tab active and click the Conditional Formatting Dropdown

Step 3. Select the Data Bars Option and

Step 4. Select one of the Gradient Fill options.

Section F – The Gantt Chart (Visual Schedule)

This is one of the coolest parts of this tool.  Having the ability to visually represent who will be doing what at what specific date is extremely powerful.  To accomplish this, we could simply fill out the project dates in Row 1 starting with column H and manually select the cells that represent the active work schedule for each row using a manual color fill but that would be silly, since we have the data and Excel gives us the ability to conditionally format the cells to represent when active work will be taking place throughout the schedule.

We will start by providing the headings for this section in Row 1 starting in Column H.  Type the first date you expect work to begin into Row 1, Column H.

Excel Format as Month/Day

With H1 selected hover the mouse over the bottom right corner of that cell and the cursor should change to a cross shape.  Clicking the bottom right corner when the cursor shows the thinner

cross shape and dragging horizontally across row one will fill in the sequential dates for the project schedule.  Make sure you go back and select these dates and format them as Month/Day.

Now, we can move on to the tricky part where we tell Excel to color fill only those cells associated with days where work will be performed.  To do this, let’s start by highlighting the entire schedule area where the color fills will appear.  Next, we will select Conditional Formatting and select New Rule.

Select “Use a formula to determine which cells to format”.  This will give us the opportunity to provide a custom formula which will let us leverage the start date in column C, the end date in column D as well as the schedule date in Row 1 Columns H onward to programatically determine which cell should be formatted to contain some color to indicate that work will be performed on this scheduled date.  The following is the formula I’m using to make this determination.

=AND(H$1>=$C3,H$1<=$D3, WEEKDAY(H$1)<7,WEEKDAY(H$1)>1,NOT(NOT(ISNA(VLOOKUP(H$1,'Non-Working Days'!$A$2:$A$7,0,FALSE)))),$F3=1)

Let’s start from left to right and examine how this formula does it’s magic. The first thing to know about conditional formatting is that we must craft a formula that evaluates to true. If the formula evaluates to true, then Excel will apply the formatting rules we specify… otherwise it will leave the cell unformatted.

We start with an ANDfunction. This provides us with the ability to specify multiple conditions, each must evaluate to true. Next, we provide two test conditions:

H$1>=$C3, H$1<=$D3

This test condition asks the question is the value of H1 greater than or equal to the value of cell C3. The dollar sign simply locks us to the first row when we continue evaluating each subsequent cell. Since we know that Row 1, Column H starts with the first scheduled project date and Row 3, Column C contains the start date for the task in row 3, we’re basically asking the question does the heading date in Row 1 column H fall between the start date and the end date for the task in row 3. If so – we’re on our way to understanding that something is going to happen on this date and we’ll need to color in the cell.

Next, we want to make sure we’re not making folks work on Saturday or Sunday and as such, we’re not coloring in any of the schedule blocks for non-working days. We accomplish this by comparing the date in Row 1, Column H with the days of the week that are between Sunday (1) and Saturday (7). the Weekday function returns a number from 1 to 7, Sunday being 1 and Saturday being 7.

WEEKDAY(H$1)<7,WEEKDAY(H$1)>1

If all goes well and we’ve passed the previous conditional tests, we know that a) work is scheduled for this date and b) we’re not on a weekend. Now, we will just want to make sure that we’re not working on a date when the client (or we) are not scheduled to work. This may be because of a scheduling conflict or because of a holiday, for example.

I chose to maintain a list of these non-working dates in a separate location – Sheet 2 for example. The formula simply performs a VLOOKUP function to locate the value of the working date in Column H, Row 1 within the list of non-working days that we created in sheet 2. I’m also checking that we don’t return a #N/A value which happens when the VLOOKUP function doesn’t find a specific value.

Lastly, I’m checking in the percentage complete column (F) for the current task to see if the value is equal to 1… which means the task is complete. I’m doing this because I want to color fill the cell with green to indicate the task is complete. We’ll duplicate the entire conditional function and use another color to indicate that work still needs to be completed on this specific project date.

If you’ve done this correctly, it should look something like the following image.
Excel Project Planning Conditional Formatting Gantt Chart
The image below shows the dates represented in the gantt section with color fills – either green for completed work, or blue for work scheduled but still not complete.

From this point, you’re mostly complete. I like to add some finishing touches, like data input validation in the schedule area to prevent users from attempting to put data there; Labels for the weekends and non-working days, etc. We could also automate some more items here – such as adding VBA macros to automate addition of tasks, but this should suffice for starters.

I’ve prepared a sample which you are welcome to download and customize to your liking. Click the following link to download the example used in this post. If you like tutorial, please consider retweeting or liking using the social buttons just under the article title.

[download id=”13″]

Tagged , , , , ,

A Simple Excel Decision Matrix | The Lynn Decision Tool

Here’s a very simple Excel based decision tool which I’ve used in many different forms for both personal and business use over the years. In this basic format, this tool will enable you to list work through a decision process between two major choices.

The tool lets you provide two basic choices (cell C2 and cell E2), features of the overall decision (these are typically outcomes or impacts of your decision between the two choices), weights for each feature and a grade for each decision by feature.

The tool also provides a calculated score based on your grades and weights and a nice little chart showing which decision came out on top.

To use the tool follow these basic instructions:

1. Provide your two choices in cell C2 and cell E2. eg: Join Army, and Go to College
2. List the features of the decision as a whole in column A rows 3 through 16. You don’t need to complete a rows. You’ll want to focus on outcomes or impacts of your decision when listing these features. eg: Long term impact on life goals.
3. Weight each feature on a scale of 1-5 based on how much you value this feature in column B, rows 3 through 16.
4. Provide a grade for each decision as it pertains to the feature. For many decisions, this will be highly subjective… don’t give too much thought initially to your scores. Try to record your immediate reaction.

Note: Don’t put anything in the “Score” columns… these are computed fields.

That’s it – very simple as I said. This tool can be built upon in many ways. I wanted to keep it simple initially and provide a Simple Decision tool. If you have other tools you use to help you make decisions, please use the comments field and let me know.

[download id=”9″]