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