I’ve been writing about how to create a BTS Project Tracker using Excel. Let me tell you a bit about why. Frequently in my career, my teams have had checklists they needed to perform at every BTS or Site in the network. These checklists were of a manageable size. I couldn’t justify a dedicated Program Manager. Nor could the cost of a full-blown project management software. Besides, for smallish projects like these, use of project planning software like Microsoft Project would be overkill. So I had to rely on my team to report and track their progress themselves. All too often I found that few of them were skilled enough with Excel to do this well.

But these types of projects are very common. I realized that I should be teaching my teams how to do these things for themselves. Not only did this seem like my responsibility. But training the team would also serve both our interests. They would acquire new skills for their careers, and I would get a more capable team, besides the better updates I would get on these projects. It was a fabulous win-win opportunity.

None of these techniques is very challenging. And all of them use basic Excel functionality that can be used every day in many other circumstances. Please join me as I work towards a complete project tracker with a comprehensive dashboard.

This Excel BTS Project Tracker Series

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

You can download my tracker to follow along with this article. Or you can use the Tracker as is, or modify it for your own projects.

Click here to download the Tracker

In the previous article, I modified the basic tracker to include Task weighting. I showed you how to use Lookup functions and create Named Ranges. You can read that article here by clicking the link below.

Excel Project Tracker with Task Weighting

If this is the first article you’ve read in this series, I recommend starting with the first article. You can quickly catch up to this article.

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

Approach

In this article, the enhancement I add to the project tracker is regionalization. Regionalization is a grouping, one in which I associate each BTS with one of four regions. To do this, just like in the last article, I’ll create a lookup for all the sites, then add columns to put the data I want into each row of Updates.

Typical Groupings

Regionalization, in this case, is a specific way to group the data. For our tracker, I’ll be able to show progress for all sites in each region. It’s easy to imagine a report from the tracker which shows project completion by region.

Other groupings are equally possible, using a similar approach. Here are some examples:

Cluster management could be imposed by assigning a responsible Engineer or technician for each BTS.
The grouping could be based upon the vendor of BTS equipment in each site.
Grouping based upon Antenna type or RF spectral band.

All these groupings can be implemented with the same approach which I’ll teach in this article. This approach is again based upon Excel Lookup functions, and a new technique I’ll show which I call Synthetic Data.

General Steps

In general, this approach has 4 steps.

Add a lookup table which uses inputted data as its index.
To the lookup table add a column containing the data you want to group by.
Add a Synthetic column for your grouping to the Updates table. This column uses a lookup to display the group name for this row of the Updates table.
Create a new Pivot table that uses this Synthetic data.

Once the new Pivot table includes the synthetic data, Project Completion is easy to show.

This approach, using Synthetic data to enhance reporting, is so fundamental that you’ll use it every day. Combining raw data with Synthetic data, then summarizing the data using a Pivot Table is so powerful in many, many situations.

Requirements

As we add regionalization to the Project Tracker, let’s update our requirements. There are 4 from the previous article, and I’ll add a requirement to regionalize the data.

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.
Completion status should reflect the different levels of effort among the Tasks.
Completion status should be displayed for each defined region.

Procedure

Here are the steps required to add a Regionalization grouping to our tracker:

Create the Sites lookup table with a column which associates each Site with a region. As always, I’ll give this range a name.
Add Synthetic columns to the Updates table. One for the Region, and one to calculate the completion percentage for each update in the Updates table.
Create a new Pivot table that uses the Synthetic data.

Create a Sites Lookup Table

To regionalize the Project Tracker, we need to create a lookup table for the Sites. This will be similar to the lookup table we created for the Tasks in the previous article.

I’ve done this on the Updates worksheet. The first column which I added included all the site numbers in increasing order. Then I added a column of Site Names. I don’t use Site Names in this article, but later I will. Besides, it is so easy to add it here. Then I added another column assigning each Site to a Region.

Lastly, I Named the range Site_Lookup. As with the Tasks_Lookup Named Range, which I created in the previous article, this makes my formulas much easier to read.

Add a Synthetic Region Column

To enable the Pivot table to report the Region and the Completion Status accurately, I need to add columns to the Updates Table. It’s important to note that adding these columns does not change the way field staff reports their update triplets. That part of the update process stays the same. So once again, we make incremental improvements to this Project Tracker, without disrupting all the work already done. Yay!

The columns I’m adding are what I call “Synthetic” columns. By “synthetic” I mean that the values are derived or synthesized from the updates using formulas. The purpose of these columns is to add the data I want to each row so that the Pivot Table can summarize this data for me.

Steps to Add a Synthetic Column

Easier just to show you what I did.

First, I inserted a column to the left of the Updates Table.
I named the column “Region”.
in this column, I want to show the region assigned to the Site_ID in this row. To do this, use a VLOOKUP function. I entered this in row 2. The first argument is the site number. The second argument is the Sites_Lookup named range. The third argument is “3”, to get the Region from the Sites_Lookup. The fourth and last argument is again “FALSE”.
Lastly, I fill this formula down to all rows in the Updates table.
I highlighted this column purple because of the formulas they contain. Note that this highlighting is an Excel Best Practice. Use color to communicate to your users (and yourself!)

Sorry, but I lied, that is not last. If you look at these formulae, you’ll see that I wrapped both of them in an IF function. The test expression for this function is another function, ISBLANK, using the site number for this row. The reason I do this is that without it, for rows which do not yet have data, the VLOOKUP functions return #N/A. I think a workbook which displays #N/A looks bad, as though I failed to anticipate the condition which causes the #N/A to display. In this case, blank rows in the Updates table. Worse, the #N/A values can mess up some Pivot Table configurations. So I use the IF(ISBLANK()) construct to display nothing when the site number is blank. I don’t like a worksheet full of #N/A errors; I think it looks unprofessional.

Add a Synthetic Weighted Completion Column

This column is added in the same way as the Region column.

Insert a column on the left. Name the header “Weighted Completion”.
In the “Weighted Completion” column I want to show what is the percentage impact of completing only this Task at this Site. To get this, the weighting for this Task must be divided by the number of Sites. Later, the Pivot table will sum all the individual completion percentages by Task and by Region.
To do this, I entered another VLOOKUP function using the 3rd column of the Sites_Lookup Named Range. Then I divide the result by the number of Sites in the project. I get that from the Named Range BTS_Count.
Lastly, I highlight the cell Purple, then fill down this formula to all rows in the Updates table.

Now that both the Tasks and the Sites are listed in Lookup tables, I can improve the automation of the workbook and remove the need to manually indicate the number of Tasks and also the number of Sites.

Eliminate Manual Data Entry

In previous versions of the Tracker, on the “Pivot” worksheet I had to enter the Site count in cell B10 and the Task count in cell B11. Entering data manually is not a problem; in every workbook you ever create you’ll almost always have to enter some data by hand. But whenever possible, you should use Excel tools to remove the need for manual effort.

In this case, we can use the Tasks and Sites Lookup tables to fill in these values. Remember that a Named Range is still just a range. And the ROWS function returns the number of rows in the range passed as an argument. I’ve done this already for both these values. Here is the formula for the count of Sites

=ROWS(Site_Lookup)

Because the 2 values are now automated, I’ve changed the highlighting from Yellow to Purple. Remember, I use Yellow highlighting to indicate manual data entry; Purple highlighting to indicate there is a formula in the cell.

Create a New “Region” Pivot Table

The Tracker now needs a new Pivot Table which includes the synthetic columns. I’ll use this pivot table to roll up the Weighted Completion by Region.

Select all the rows and columns in the Updates table which have data.
Click INSERT->PIVOT TABLE. The Create PivotTable dialog box will have the input data already selected.
Where the dialog box says “CHOOSE WHERE TO PLACE THE PIVOT TABLE”, click “NEW WORKSHEET” and click OK.
The Field Name box should open. Drag field Task and drop it on the Columns area.
Also, drag field Region and drop it on the Rows area.
Lastly, drag field Weighted_Completion and drop it onto the Values area.
Click the small letter “i”, then click “Number…”. Select a percentage format with a single decimal place. This hides those long decimal values and makes the table more readable.
Rename the worksheet to “Regions”.

Create a Regional Completion Table

That’s all we need to do to create the Pivot table. You can now read the Regional Completion directly from the “Grand Total” column. However, the number of columns separating the Region and the Grand Total values is so great that it makes sense to setup a separate table showing only those 2 values for each region. That was you can clearly see the association between the 2 columns.

I’ve done that, once again using the magical GETPIVOTDATA function. To use this function, click in the cell where you want to display the GETPIVOTDATA value, let’s use cell O3. Enter an “=” sign, then click in the Grand Total column of the Pivot Table. If I click in cell L3, the function appears.

=GETPIVOTDATA("Weighted_Completion",$A$1,"Region","East")

The value 17% is displayed.

Extend to Other Regions

That’s fine for the East region. But what about the other regions? And what if the name of the regions were to change? In other words, simply clicking in the Pivot table to get our GETPIVOTDATA function is too inflexible. The solution is for arguments in the GETPIVOTDATA function to be cell references, rather than the hardcoded values that are defaulted when clicking in a cell.

Instead, what I want is something like this:=GETPIVOTDATA("Weighted_Completion",$A$1,"Region",N3) I’ve replaced “East” with the cell reference N3. The value in cell N3 is “East”, so the result of the formula is the same as before. But with a cell reference as the 4th argument, I can now drag this formula down 3 cells to fill the formula for the other regions.

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 table. A popup menu will display. Select “Refresh Data”. That’s it! The Pivot Table now reflects the new Updates.

Conclusion

In this article, I showed you how to add Regionalization using Synthetic Data to extend the power of Pivot tables. By associating each Site with a region, the Pivot table can group the completion status by Region as well as by Task. We used VLOOKUP to get the region associated with each Site.

Click this link to read the next article in the series

Excel BTS Project Tracker (Date Buckets)

Coming Up Next

In the next article in this series, I’ll show you Date Bucketization. Date Bucketization is similar to Regionalization. We add a Synthetic column to the Updates table to group all dates into monthly buckets. Bucketization is another very commonly used technique to enable Pivot tables to do more of the work.

Looking a bit further ahead, I’ll show you Dynamic Named Ranges, Charting, and ways to generalize this so that you can more quickly adapt it to other projects. I also plan to add a Dashboard.

The ultimate goals for this or any Tracker should be to minimize the amount of work routinely required to deliver the maximum amount of information. And not just any information. I call it “Decision Ready Analysis.” Decision Ready Analysis is just what it says: the analysis presented is actionable. A decision can be made based upon the information at hand. No rework, no more processing. A decision can be made. That’s huge.

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