Have you ever tried to use Excel to track the progress of a project? How did you do it? Did it work out OK? Or was it a huge mess, one that barely succeeded in reporting the status of the project?

Often, before people start using Excel, they imagine a piece of paper that might represent what they want to accomplish. Then, they set about to create that piece of paper using Excel. But this approach takes no advantage of Excel’s power to automate, simplify, and beautify.  They end up with something too labor-intensive, error-prone and ineffective.

I’d like to show you why Excel is truly outstanding for tracking projects. It’s also completely fantastic at creating dashboards, doing analysis, and at communicating complex concepts to non-technical audiences. But you have to know how to use it properly. I know you can do this.  Follow along with me to learn what you need to know about Excel.

This is the second article in my “Excel BTS Project Tracker” series.  In this series, I’m creating a completely functional project tracker using Microsoft Excel.  With each new article in the series, I enhance the previous version of the Tracker and teach you a couple of new Excel techniques at the same time.

You can download the tracker to follow along with this article.  You also can use the Tracker as it is, and it should be pretty straightforward to modify it for your own projects.  Below is a link to download the Tracker.

Download Excel BTS Project Tracker

In the previous article, I created a basic, but fully-functional project tracker in Excel. You can read that article here by clicking the link below.

Excel Project Tracker (with the Coolest Excel Function Ever!)

Approach

In this article, I’m going to enhance my initial project tracker using Task Weighting.  Weighting is a way to account for differences in the amount of work required to complete each task.  The first article assumed that each task required the same level of effort.  But in reality, that is rarely the case.  It is far more likely that the effort to complete each task is NOT the same, and therefore weighting them will result in a more accurate assessment of project completion status.  Possibly much more accurate.

Adding task weighting to the project is super simple in Excel. To do it, I’m going to introduce you to Excel Lookup functions.  A lookup function is used when you have a known value that you want to use to find a related but different value.

This known value is used as an index into a list or an array.  The lookup function then uses the value in another row or column of that same range.

Excel’s HLOOKUP function looks in the first row of a range for a specific value. If it finds that value, the function returns the value in another row of that range, using the same column as the found lookup value.

VLOOKUP looks for the lookup value in the first column, then returns the value in another column of the range, using the same row as the found lookup value.

If the Lookup value is not found, the #N/A error is displayed.

It should be clear that the H in HLOOKUP means the data is in a horizontal layout, and the V means vertical.  Myself, I tend to prefer vertical layouts.  But you can usually use whichever layout is most comfortable for you.

Another new thing I’ll show you in this article is Excel Named Ranges. A Named Range is just what it says: Excel lets you assign a name to a range of cells. Named Ranges help to improve the readability of your formulas. That makes them easier to understand and to debug.

Requirements

In the first article, I had 3 requirements for the project tracker. Here they are again, with a fourth added to include the weighting.

1. It should be easy for workers in the field to submit Updates.
2. The PM should be able to easily import the Updates to the project tracker.
3. Project status should show automatically, without requiring further calculations.
4. Completion status should reflect the different levels of effort among the Tasks.

In this article, I’ll show you how to weight each Task and how to include that weight in the calculation of the Project Completion %.

Move the Pivot Table

First, because I’m adding functionality to the initial Tracker, I want to make a little space for the weighting and additional calculations. So, I’ll move the Pivot table to its own worksheet.

Here’s how I did that:

1. Insert a new Worksheet, rename it “Pivot”
2. On the “Updates” worksheet, select and cut cells K3:U12. This range includes the completion status calculations
3. Click cell A3 on Pivots worksheet and paste.

This makes the Updates worksheet a little less cluttered and makes more room for the task-weighting functionality.

Create a Task Lookup

In the space cleared on the Updates worksheet, I’ve inserted a range that lists all the Tasks. The Task Names are entered in the first column of the range, and the weighting for each Task is entered in the second column. Later, a VLOOKUP function will use this range.

One more point about Excel’s lookup functions: the lookup values must be in alphabetical order. Otherwise, the Excel’s lookup functions might not do what you expect. So enter the Tasks in alphabetical order, or sort the Tasks after they’ve all been entered. I’ve entered the Tasks alphabetically in column J. I also added a descriptive header row. Then, in column K, I added a weighting associated with each task.

Keeping in mind one of the Excel Best Practices, each cell in the Weighting column is highlighted yellow, to remind the user that they can make changes to these cells.

The sum of all the weighting should equal 100%. I’ve added the SUM function after the last Task row to verify that the total weight is 100%.

Create a Named Range

The last thing I need to do to this range is to give it a name. It’s not actually necessary to name this range. You can use a range like J3:K11 and the VLOOKUP function will work just fine. But when you look at the formula, the range J3:K11 doesn’t tell you anything about the data. This makes it harder to troubleshoot or to explain to someone else. By naming the range appropriately you can signal to the reader what the range means.

There are several ways to name a range. The easiest is to simply select the range, then enter the name in the blank area at the far left of the screen directly above column A.

Using Named ranges is an Excel Best Practice, and I recommend using them. The benefits of using Named Ranges include more readable formulas, easier debugging, and self-documenting code.

I’ve included the Home menu and the top of the Updates table in the screenshot so that you can see clearly how to name a range.

Apply Named Ranges Throughout

Now that we know about Excel Named Ranges and how to create them, I should go back and apply that technique wherever else I can in the tracker.

On the “Pivot” worksheet, the “% Complete” formula in cell B12 uses the values from cells B10 and B11.

=GETPIVOTDATA(“Date Completed”,$A$3)/B10/B11

Let’s name each of those cells. I’ll name cell B10 “BTS_Count”, and cell B11 “Task_Count”.

I can replace the row and column references in cell B12 with the named ranges. Now the formula looks like this:

=GETPIVOTDATA(“Date Completed”,$A$3)/BTS_Count/Task_Count

That’s a bit more readable.  And reminds me of another Excel Best Practice. When you learn a better way to do something, always use that technique and back into your other Excel workbooks and replace the old technique with the new technique you’ve learned.  That way your entire portfolio improves.

Add Task Weighting to the Calculation

Now we need to add the weighting to the calculation of the Project Completion percentage. There a several ways to do this. The simplest way is to 1) calculate the completion percentage of each individual task, 2) apply the weighting for that task, 3) then sum the individual task completions.

I can calculate the completion percentage of each task by dividing each value in the pivot table by the number of sites. I’ve done that in row 6. Notice in the formula that I’ve used the Named Range “BTS_Count”.

Next, in row 7, I multiply the completion percentage of each Task by the weighing for that Task to display the completion percentage of the project for that task. That wording might not be completely clear, so I’ll write again in a different way.  This value is the amount of the overall project that completing this number of tasks contributes.  

This formula is where we use the VLOOKUP function.  Here is the format of VLOOKUP function. The arguments are the same for the HLOOKUP function if you prefer. I’ve added spaces here to make it more readable here.

=VLOOKUP( lookup, range, offset, match)

1. lookup. The specific value to look up is called the lookup value. This is the first argument to the lookup function.
2. range. The lookup range.  This is the group of cells to be searched for the lookup value.
3. offset. If the lookup is found in the range, the offset says where to find the result we want.
4. match. The match argument indicates whether lookup must match exactly, or if an approximate match will do.  Enter FALSE or 0 to only accept an exact match.  Specify TRUE or 1 to accept an approximate match.

You can read more about the VLOOKUP function at ExcelFrog.  ExcelFrog has many other great Excel tutorials, too.  I’ve also adopted ExcelFrog’s markup scheme for Excel, to make this article more readable.

Using this format for our own VLOOKUP function in cell B7 looks like this for the first Task, “Antenna System”

=B6*VLOOKUP(B4,Task_Lookup,2,0)

In this formula

• Cell B6 is the percentage completion of this task.  That means the number of sites for which this task is completed divided by the total number of sites. 
• Cell B4 is the column header of the Pivot table for the “Antenna System” Task. This is the lookup value searched in the range.
• For the range value, I used the Named Range “Task_Lookup”.
• I’ve used 2 for the offset value.
• I’ve also used a 0 instead of typing out the word “FALSE” for the match value in the fourth argument.

Once the formula was complete in this cell, I dragged it to the right until the B4 argument displays J4. This now includes all the Tasks in our project.

I retained the former project completion calculation so that you can see the impact of weighting the Tasks. Comparing the difference between the two, the weighted project completion is 4 percentage points lower than the unweighted project completion percentage. With a large number of sites that difference could shift the overall project completion date by several days or even weeks.

If you’re not convinced that Task Weighting makes a difference, try changing the weighting values in the Task_Lookup named range to realize a bigger difference. Task Weighting is a legitimate way to improve accuracy.

Up Next

In the next article in this series, I’ll add regionalization to the project tracker.  Most mobile networks are large enough that groups of BTS will be assigned to a geographic area.  Even if the network is not large enough to justify a geographic segmentation, it will still have BTS which are grouped together, whether regionally, or in RF clusters, or perhaps assigned Engineer or Technician, or maybe by vendor or firmware version or antenna type.   

Whatever segmentation exists, what I’m calling “regionalization” is the Excel technique to incorporate it in your project tracker.  Watch for that article coming up soon. 

Conclusion

In this article, I showed you how to use Named Ranges and Lookup functions. We used VLOOKUP to get the weighting associated with each Task. I’m sure that almost every time you use Excel you’ll have an opportunity to use Named Ranges and Lookup functions. I use them all the time. You should, too.

Read the Next article in this series.

Excel BTS Project Tracker (This Technique is Much More Useful)