## Better Way to “Catch” Multiple Slicer Selections in a Formula

It’s Actually VERY Simple to Catch Users’ Slicer Selections in Excel Formulas,
IF You Are Using PowerPivot (AKA the 2013 Data Model Feature)

### A Popular Topic

No, I don’t mean things like “Fox Urine” or “Face to Anogenitaled” – those are pretty funny of course, and they come up in my job because I consult for my scientist neighbor on his lab rat projects.

But no, I’m here to talk about something even more popular than Fox Urine

Every day, one of the most-read topics on this blog is some variant of “I want to catch slicer selections in formulas.”  This has been covered in at least three different posts:

There have also been a number of comments on each post that suggested alternate (and often better) ways of doing things.  I’ve never been terribly comfortable with that third post in particular, the one dealing with multiple selections.

Funny thing is, I used a new technique (for me) in the Calendar Chart posts, but never went back and called it out explicitly.  Today I am going to correct that omission.

## Conditional Formatting via Slicers, Part Two

Each Number Refedit in the Edit Rule Dialog Points to a Different Cell on the Sheet

### Correcting for that bug

In the last post on this topic, I discovered what I think is a bug in Excel 2010 conditional formatting, one that prevented me from using the Percentile threshold type with a cell reference.

But the Number type works great, so if I can get the actual [Profit] value for, say, the 80th percentile Model Name into a cell, I can reference that.

And that’s precisely what those three cells in the image above contain:

Cell F2 Contains a Cube Formula that Returns the Measure [Profit Required for Green CF]

OK, so how do I calculate that [Profit Required for Green CF] measure?

## Conditional Formatting Controlled via Slicers!

Yes folks, we’ve seen all manner of parameterization by slicer.  We’ve even seen sort by slicer.  But now it’s time for me to track down a hunch I’ve had for awhile now:  we can also control conditional formatting via slicers!

At the 65th Percentile, Model Name Profits are Being Shaded Green

Getting Stricter:  Use the Slicer to Set the “Green Threshold” to 95th Percentile

## Last Mini-Post for the Day: Check out Chandoo’s PIVOT version of the Calendar Chart!

I thought I had seen everything.  I had not.  Chandoo, we bow before you: Yes, That’s a PivotTable!  Click the Image to View the Post on Chandoo.org (Oh, and I really need to figure out how to make these animated…