Adding Conditional Formatting to a Portfolio Milestone Report

After writing that last post, I was still vaguely dissatisfied with how the report turned out.  My goal in developing the report was to add conditional formatting to the PivotTable to increase the richness of the data available in the view.  I felt that I had failed to do that.

Now, my wife is fond of throwing Mongolian platitudes at me on occasion, and in this case, the one that kept coming to mind was “Davs heevol ustil; ajil heevol duustil,” which is one of those folksy sayings that translates roughly into “If you’re going to put salt in the water, make sure it dissolves – and if you’re going to do a job, make sure you complete it.” (And yes, it sounds much better in the original form.)

So, with thoughts of salty water swirling in my head, I went and played with the report for a couple more minutes, and was able to work out the conditional formatting mechanism in Excel 2010.  Note that this may/may not work in Excel 2007 or Excel Web Services, but I’d be curious for feedback if anyone tests this out.


…so when last we visited this topic, we had an Excel based report showing all of our portfolio milestones along with a couple of key icons based on the Finish Variance field.


Following the same instructions as in that article, I created a second PivotTable on a second worksheet.  This PivotTable includes TaskPercentComplete and a custom Finish Variance field (=TaskFinishVariance/8).


Don’t worry too much about formatting the table as nobody is going to see it.

Now we go back to our first PivotTable, click on a Finish Date and add Conditional Formatting.  Set the field to display green when the TaskPercentComplete cell equals 100%.


See how the Sheet2!B4 references the appropriate TaskPercentComplete cell for that Stage Gate?  Note that you’ll have to remove the “$” which are added by default to the equation to fix the reference, i.e. the default will be “=Sheet2!$B$4=100,” but you’ll change that to “=Sheet2!B4=100.”

Go ahead and add more rules.  Go into the Conditional Formatting dialog box to modify the order that the rules are applied in.


After working through all of that, in my example I have the following rules:

  1. All stage gates that have been completed appear as green.
  2. All stage gates with a finish variance > 10 are red.
  3. All stage gates with a finish variance between 0 and 10 are yellow.
  4. All stage gates with a finish variance < 0 are green.

Which appears as follows….


….much easier to read than what I had before, I think.

Adding Conditional Formatting to a Portfolio Milestone Report

4 thoughts on “Adding Conditional Formatting to a Portfolio Milestone Report

  1. Hi Andrew,
    A great post. I think you had a query before on how well this worked in Excel Services – I’m glad to say that I’m using it for the equivalent of 20 gates over 100’s of projects. We pivot on the gates we want to see, and slice on certain project parameters. The conditional formatting works, though it took some time, and eventually I used just a single sheet, and hid the columns I didn’t want anyone to see. Also, I didn’t bother with a calculated field, I just took the sum of the taskfinishvariance worked out the right number of minutes for my chosen days….

    For the record, SharePoint 2010 etc…

Leave a Reply

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

You are commenting using your 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