PowerPivotPro is Coming to Phoenix

February 20 - 22, 2018

Registration for 2018 Public Training is now open!


**Use the discount code “3ORMORE” when signing up 3 or more people.

FEBRUARY 20 - 21

Foundations: Power Pivot & Power BI

Instructor: Kellan Danielson

Super charge your analytics and reporting skills with Microsoft’s dynamic duo. Designed to handle huge volumes of data, these tools will transform the way you work! Two Days in our class and you are EMPOWERED!

  • Learn Microsoft’s secret weapon behind Power Pivot & Power BI: DAX
  • Taught by Kellan Danielson – PowerPivotPro Partner and Vice President of Client Services
  • You don’t need to be an IT professional – most of our students come from an Excel background

FEBRUARY 20 - 21

Level Up Series: Advanced DAX

Instructor: Ryan Sullivan

The Advanced DAX Course was such a hit in the first half of 2017 that we’ve expanded the course to 2 days!


  • This advanced DAX training class is taught completely in Power BI Desktop.
  • Students are encouraged to take our Foundations course and have hands on experience with the DAX language.
  • Taught by Ryan Sullivan – Principal Consultant.
  • Class material drawn from usage of Advanced DAX applications while consulting with hundreds of international firms.


Level Up Series: Power Query for Excel & Power BI

Instructor: Krissy Dyess

The second class in the series is our Level Up Series is Power Query for Excel & Power BI.

  • Students are encouraged to take our Foundations course and have hands on experience with Power Query in Excel or Power BI Desktop.
  • Taught by Krissy Dyess – PowerPivotPro Principal Consultant and Phoenix native!
  • We will cover common to unique business challenges made easy with Power Query’s data wrangling capabilities.
  • Intermediate to Advanced Level Power Query best practices distilled into easy to understand patterns to apply to your most common business challenges.
PowerPivotPro Logo

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

  Subscribe to PowerPivotPro!


Rob Collie

One of the founding engineers behind Power Pivot during his 14-year career at Microsoft, and creator of the world’s first cloud Power Pivot service, Rob is one of the foremost authorities on self-service business intelligence and next-generation spreadsheet technology. 

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