PowerPivot Makes it Easy to Toggle Conditional Formatting On and Off via Slicers

The One and Only “Sam Rad”

imageAt the Microsoft MVP Summit last week in Seattle/Redmond, I got to see and hear a lot of cool things.  But none of that compares to seeing old friends and colleagues – some of the most extraordinary people I have ever known (and ever WILL know).

People like Sam Radakovitz for instance, aka “SamRad.”  A veteran Excel team member who briefly left to do other things but is now back on Excel.  This is Very Good News.

(Actually this is a theme – a lot of former Excel program managers in particular have “come home” from other places and have rejoined the team.  I expect this next release of Excel to be something special).

In addition to being a numbers/technical guy, Sam is very visually gifted.  He takes bland stuff and makes it sing.

We all could learn a thing or two (or a hundred) from Sam.  Even in spreadsheets, presentation quality has tremendous impact.


Sense of Humor

Sam will go to great lengths to make you smile.  For instance, he gave a presentation at the Summit last week on making your spreadsheets “top notch.”  And in this presentation he had a section called “Can Excel Help Rob Find Love?”  He had built a game show type of spreadsheet where he asked me all sorts of questions in front of the audience to find my Perfect Match.

Nevermind that I am married.  He claimed to not have known that.  At one point in the presentation I answered something to the effect that I like vegetables better than candy, and this picture suddenly appeared on the screen:


Yeah that’s a picture he harvested from my wife’s Facebook page.  He replaced her with broccoli.

Many Tricks.  Let’s start with “CF Toggle.”

OK back to serious business.  He showed a bunch of cool tricks that are relevant to people like us.  I’m going to re-share those here on the blog over the next couple of weeks (with proper attribution to the master!)

How do we use a slicer to turn conditional formatting on and off?

First, a disconnected table in the PowerPivot window, created via copy/paste:


A table named CFToggle

Next, a measure:

   [CF On Off]=

Add the Label field as a slicer:


CFToggle[Label] field added as a slicer

Then a cube formula to “fetch” your selection into the Excel grid:


Then a conditional formatting rule (in the pivot) that references the cube formula cell:


Depending on What’s Selected on the Slicer, The Color Scale CF Rule Gets “Blocked”

Pretty cool eh?  This is of course similar to an older post where I controlled CF “threshold” values via slicers, but the notion of turning CF on/off altogether was brand new for me.

Download the Workbook!

Here’s the workbook in case you’d like to inspect it:

Click Here to Download the Workbook