The One and Only “Sam Rad”
At 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]=
MAX(CFToggle[Value])
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:
