One of the most common projects in Mobile Telecoms is to complete a checklist of tasks for each BTS in a group.  In this series of blog posts, I’ll show you how to create an effective Tracker for this type of project using Excel.  If you work with Excel, and especially if you are a Technical Manager, this series is written for you.

You’ll not only get a reusable project Tracker, but also some new Excel skills to save your time, reduce your workload, and maybe even get recognized for the quality of your work.  You’ll use these techniques again and again.

With this first post, I’ll create a complete project tracker.   I want to show you how easy it is to do using Excel.  This will be a fully functional project Tracker, which you can use immediately.

In the posts to follow, I’ll refine and improve this Tracker by showing a new Excel technique with each new blog post. Each new technique will be added on to the existing Tracker so that it gets better with every article.

When we’re finished, you’ll have a great tool which you can quickly adapt to track new projects like this. You’ll also know some new ways to use Excel that will help you in many other areas.

Project Overview

This project consists of a few milestones or tasks, I’ll call it a “Task” in this article.  Each Task must be completed for each BTS or Site, which I’ll call a “Site_ID</span”.

There are several requirements for this project.

• It should be easy for workers in the field to submit Updates.
• The PM should be able to easily import the Updates to the project tracker.
• Project status should show automatically, without requiring further calculations.

The Excel technique we’ll use for this initial tracker is called a Pivot Table.  Pivot Tables in Excel are fabulously useful tools for summarizing data, without having to enter individual formulas.  This makes Pivot Tables easy to use, reliable and accurate: no formula errors to worry about.

Create a Table of Updates

Let’s begin by creating a simple, 3-column table.  This table will hold all Updates.  Updates are written by the workers in the field, the people actually performing the tasks.  These are probably technicians, contractors or maybe RF Engineers.  They do the work, complete a task, and document it with an Update.  Then, they periodically submit their updates to the Project Manager (PM).  Maybe that’s you.

Download my workbook to follow along.

Open a new Excel workbook.  Rename worksheet1 by double-clicking in the tab and typing “Updates”.  This is an Excel Best Practice, always give worksheets informative names.  Never leave a worksheet called “worksheet1”.

Setup a 3-column table starting at cell A1 with these column names:

• Site_ID, (cell A1)
• Task, (B1)
• Date_Completed (C1)

The Site_ID should be whatever unique site identifier is already used in your network.  Don’t worry about using the site name or cluster or regional grouping.  We’ll add those in a later blog post.  For my Site_ID I’ll use a simple numeric listing starting at number 1 going up to 23.

The Task column contains the agreed milestones for the project.  It is important that all the field workers know the valid Tasks so that they will submit Updates accurately.  If either the Site_ID or the Task is reported incorrectly then project status will not be accurate.

I usually highlight manual input cells yellow, to remind me that is a place to enter data.  This is another Excel Best Practice.

Date_Completed is the date this task was completed, duh!   When recording Updates, Field workers should all use the same date format, such as dd/mm/yy, to avoid errors when pasting into Excel.

Create a Pivot Table

This pivot table summarizes the Updates table I created earlier.  It counts the number of sites that have completed each task.  Here are the steps to make it:

1. Select all the rows and columns in the Updates table which have data.
2. Click INSERT->PIVOT TABLE.  The Create PivotTable dialog box will have the input data already selected.
3. Where it says  “CHOOSE WHERE TO PLACE THE PIVOT TABLE”, Click
4.  “EXISTING WORKSHEET”,  move the cursor to the TABLE/RANGE input box, then click cell I3 on the Updates worksheet.
5. The Field Name box should open. Drag field Task and drop it on the Columns area.
6. Drag field Completed_Date and drop it onto the Values area.  Note that this Field already says “Count of Completed_Date”.  That’s exactly what we want.

That’s all we need to do to create the Pivot table.  When the Updates table changes, say after pasting in new Updates, the way to reflect these changes in the Pivot Table is to right-click inside the Pivot.  A popup menu will display.  Select “Refresh Data”.  That’s it!  The Pivot Table now reflects the new Updates.

Calculate Project Status

To properly show the project status, we need to know how many total Site_ID–Tasks there are.  We can calculate that from the number of Sites_IDs and the number of Tasks.

1. Click in cell K10.  Enter “Number of Sites”.
2. Enter the count of Sites in cell L10.  For us, the count is 23.
3. Click in cell K11.  Enter “Number of Tasks”.
4. Enter the count of Tasks in cell L11.  For us, the count is 9.
5. Click in cell iK2.  Enter “% Complete”.

The last thing we need now is the number of Tasks which are completed.  But the Pivot already shows that there in Cell S5.  It would be best to show that count alongside the project completion calculation.  It also would be nice to have a way to include that in the Project Status formula automatically.

And we can!   This is one of the Coolest Excel tricks you’ll ever see.  Once you have this technique down, you’ll use it every day.

1. Click in cell L12.
2. Enter an equals sign (“=”).
3. Then move the mouse to cell U5 and click.  This formula appears next to the “=“ sign in cell L12:  GETPIVOTDATA(“Date Completed”,$K$3).  You can click the “%: formatting icon to display completion status as a percentage.
4. The GETPIVOTDATA() Function is used to extract data directly from a Pivot Table.  Providing FIELD arguments to the GETPIVOTDATA() function makes it very powerful and flexible for automating reports and trackers like this one.  We won’t do that here.  The function is a little complicated and the best way to learn about it might be through simple trial and error.  Lots of trial and error.  Maybe a separate blog post dedicated to it would be helpful.

I suggest you read the Excel help documents, and experiment.  The more you experiment and try this function the more it will begin to make sense.

Here is a good tutorial on Pivot Tables: 8 Excel Pivot Table Examples – How to Make a PivotTable!

For now, because we give GETPIVOTDATA() no FIELD arguments, and because the VALUES field in the pivot table is set to COUNT, this function returns the Grand Total for the Pivot Table, which is exactly what we want.

Project Process

We’ve set-up the workbook. The only thing left is to have instructions for all the project participants.  Here is how the project will proceed day-to-day:

1. Workers will perform each task at each given site.  No order for performing the tasks has so far been specified.
2. When any Task has been completed at any site, the worker will write down the Task, the Site_ID and the Completed_Date.  This group of 3 data elements is called an “Update.”  An Update is a block of 3 text data elements separated by commas.
3. Periodically, once each week or maybe every day, workers will submit all Updates to the PM.   Submit your  Updates to the PM via IM, or Email, or Slack, or whatever method is normal for your company.
4. The PM will copy the Updates and Paste them into the Excel project tracker, in the Updates table.
5. Then, right-click in the Pivot table and select REFRESH DATA.

That’s it!  The updated project status displays immediately in Cell L12, with no other action required.  You now have a working, functional project tracker.  It is pretty basic, I’ll admit.  But to have a complete working application in only one blog post is pretty cool, don’t you think?

Limitations

Of course, there are some shortcomings to this tracker.  Most of them I’ll address in later blog posts.  Some of them are caused by trying to keep this post short and sweet.

• The data source for the Pivot Table should include some blank rows so that when new updates are added, they’ll be included when the Pivot is refreshed.
• The Site_ID and Task counts we used to calculate the Project completion status are hard-coded.  If at a later time in the project, another site or another task is added to the project, the status calculation will be wrong.  The values must be manually corrected.
• Formatting.  For this post, I did very little to make the status look good or presentable

Next Up

In this series of blog posts, I’m developing a project tracker.  I’ll refine this Tracker in future blog posts.  In the next post, I’ll address the reality that not all tasks are created equal.  Some tasks require more work to complete than other tasks.  This is called “weighting”.  In the next blog post, I’ll add the ability for the Project Tracker to weight to each task and display the project completion status more accurately.

Below is the link to the next article in this series.

Excel Project Tracker with Task Weighting