skip to Main Content

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

Rob Collie

One of the original engineering leaders behind Power BI and Power Pivot during his 14-year career at Microsoft, Rob Collie founded a consulting company in 2013 that is 100% devoted to “the new way forward” made possible by Power BI and its related technologies. Since 2013, PowerPivotPro has rapidly grown to become the leading firm in the industry, pioneering an agile, results-first methodology never before seen in the Business Intelligence space. A sought-after public speaker and author of the #1-selling Power BI book, Rob and his team would like to help you revolutionize your business and your career.

This Post Has 13 Comments
  1. Okay, that’s it, I’m definitely going to have to vehemently disagree with you here. Vegetables (in general) are NOT “better than” candy (in general), and we can pivot that data based on lunchroom polls from elementary schools worldwide (the poll data is available via Azure DataMarket). Yes, you can find some candy (like Payday) that under-performs versus some vegetables (like corn on-the-cob), but vegetables, especially green vegetables, consistently rank well below pasta and fruit in just about any poll taken since 1850. Statistics don’t lie.

  2. After downloading the file and opening it in Office 2013 x64 and allowing the automatic PowerPivot upgrade, the CUBEVALUE formula resolved to an error and the toggle would not work. To fix it I needed to change the connection from “PowerPivot Data” to “ThisWorkbookDataModel”. Not sure what caused that.

    1. Oh, sorry Ron. MS changed the name of the connection between 2010 and 2013.

      Should I say “good catch?” 🙂

  3. Hey Sam, it’s good to see that you’re alive and well 🙂 I’m a bit disappointed that your great date picker idea never made it into Excel’s data validation function, but’s that’s a different matter…

    Here’s the thing I like about your CF trick. I’ve always had issues with displaying data over the conditional formatting. It’s virtually impossible for the brain to focus on the numbers and CF pattern at the same time. It’s hard work to internally filter out the numbers when you want to focus on the formatting and vice versa. With data bars, it’s doubly difficult because it’s very hard to see small differences in bar length when the numbers run out beyond the end of the bars.

    Your trick can be used in such a way that when you turn on formatting, the numbers don’t display. For data bars, just choose to display the bars only, and ensure that a number format is explicitly set for when the formatting is off (otherwise, the display is blank!). For color scales, the default number format for values would be ;;; This way, numbers don’t display when the formatting is on. For the off condition, you would set an explicit number format in the same manner described for data bars. From then on, you can focus on number or formatting pattern independently, with a single mouse click!

    1. Oh, wow! The number formatting thing. When I started this post Colin I was gonna do icons or data bars and have the numbers turn on or off just like you specified, but I ran into that “numbers go blank” problem and then retreated to color scales with the numbers always on.

      HUGE, thanks Colin!

      1. Oops Rob, I just realized that the post is yours and not Sam’s. Sorry ’bout that. It seems that anytime you post a slicer trick (like this and TopN), I instantly know how I’d like to extend it in a way that I find very useful and exciting. My thanks to you. Please keep ’em coming.

  4. Just a small thing, but I would use 0 for “Off” and 1 for “On”, and use Min() in the measure to make ‘0’ or “off the default value.

      1. Way off the mark 😉

        More like Excel user who stumbled over Powerpivot two years ago, and nothing has ever been same since….

  5. Love this trick – thanks to Rob and Sam! Wanted to know though – it looks like Sam’s workbook (gazing lovingly at Broccoli) is running in Excel, but I know I’ve never managed to build a workbook that looks that sleek. Is that all wrapped into an App for Office? Any way to share some details of how that was built?

Leave a Comment or Question