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:
- All stage gates that have been completed appear as green.
- All stage gates with a finish variance > 10 are red.
- All stage gates with a finish variance between 0 and 10 are yellow.
- 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.