Excel Scorecard Style Compliance Report for Project Server 2010

Ok, so I’ve given you some neat queries you can use to check the project schedules hanging out in your PMO to make sure that they aren’t broken, or breaking stuff.  You can use those reports and a myriad of others in one workbook with a main tab devoted to showing a high level view of how PMs or Projects compare to the expected stadard.  There are three important pieces to this:

1. Tabulate those reports!

Each report should have its own tab, and each report should be tabulated and have ‘repeat item labels’ set it the preferences for all fields.  That will ensure that your reports look like this:

2. Create a list of PMs or projects – use a query!

Using a very simple query that pulls like two pieces of data, create a “master” tab which only has that Pivot Table on the left side.  Only the first two (or however many) columns will have Pivot Table data.

3. Use a smart and dynamic formula to score the areas

In the column to the right of your data, you’ll want to put a title at the top.  In my case I start with “Missing Baseline”.

Then, I inserted this formula:


The logic of this one whouldn’t be too hard to follow:

If B2 is not blank, count stuff that matches it on another sheet. If A2 isn’t blank, count stuff that matches it on another sheet.

Drag that sucker down.  Drag it down forever.  Don’t worry, blank lines will be blank!

Now you have:

Notice that when you expand and contract your PM name or whatever, the column updates and your data stays pretty:

Create a column for each happy little tab and BUME.

Oh, I almost forgot – since the pivot table is only the first two columns, you have to be fancy to get the shaded rows to go across where the PM names are.  Conditional formatting with a formula like :


should do the trick – as long as you don’t have crazy semi-colons in your other rows of data somewhere.  You can also add some conditional formatting to highlight out of spec totals, and then… well…there you go. 

You can also use the instructions on my post about only showing certain things in Excel Web Access, set a print area = to the amount of columns across and down for basically ever, and then your PMs can view their “scorecard” any time.  You get the gist.  Ok, go have some fun with this!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s