## David Hager on Dynamic Conditional Formatting

### Intro From Rob:  Greetings from Vegas!

Well ok, Vegas isn’t tons of fun when you don’t leave your hotel room very much, but hey, I have a great view of castles and downtown Manhattan.  I’m a little worried that this photo may open a wormhole into some alternate universe however:

Hey Wait a Second – How Is this Picture ALREADY in
the Blog Post Being Written in The Same Picture???  My Head Hurts.

Anyway, today David Hager has graciously stepped to the plate with a guest post.  He’s seen me goofing around with conditional formatting a lot and he’s got some advanced techniques to add to the mix.  Today’s post sets the stage for that.

It all revolves around the capability of CF (Conditional Formatting) to use formulas as the “decider” of whether or not to format a cell:

This CF Rule Type is Important to David’s Techniques

All right, take it away Mr. Hager…

### CHANGING CONDITIONAL FORMATTING LOGIC USING DROPDOWNS

There has been a lot of demos and discussion on this blog relating to the innovative use of conditional formatting (CF), particularly in connection with PowerPivot.

The technique of CF in Excel is especially powerful where formulas are used to define the desired TRUE/FALSE condition to be applied. It occurred to me that in this era of data visualization and controls (such as slicers) for altering the data view, there was no user method for changing the underlying logic of applied conditional formats. As an example, say that on a worksheet that cell D7 has a CF formula of =D7>8. There is no way to change the CF to =L7>8 unless the CF control on the Ribbon is opened and the formula is manually changed. Further, a CF formula with both conditions ( =AND(D7>8,L7>8) ) may be what is desired, and the user may not know how to do this. Thus, I decided to create a method that allows for the selection and changing of CF conditions from dropdowns using data validation lists.

## 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?

## Display User’s Slicer Selections: A Macro to Automatically Create the Formulas

Note the “Readout” that Displays the User’s Slicer Selections:
Now Do This for Every Pivot in Your Workbook With One Click!

### A Common Trick, Now Automated

This is something we do all the time at Pivotstream – we write formulas that capture user slicer selections and then display those selections back to them.

We do this via hidden report filters:

(Yes, we could also do this with cube formulas, but we started out (literally years ago now) using this approach and we’ve just kinda stayed with it.  I’m not sure cube formulas would be better, but they might be.)

### Why is the Readout Useful?

Why do we do this?  Well, for one thing, the Download Snapshot feature on the server does NOT download the slicers – you get a big blank white space where the slicers were, which isn’t terribly helpful.  It leaves you wondering what you had selected.

It’s also useful when there are slicers on other sheets impacting your current sheet.  And even on a single sheet, it’s often nice to have a compact readout of your selections without having to scroll (or even scan with your eyes) to see what selections you have made.

## 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

## PowerPivot Calendar Chart in Excel: Specific Steps for Adapting it to Work With YOUR Data

Modifying This to Work With Your Existing Workbook Isn’t Hard

### Continuation

Given the continued popularity of the Calendar Chart and the post I did on its anatomy, I thought I’d continue today with a more pragmatic “how do I adapt this to work with my data?” post.

OK, so you like the calendar chart but you don’t want to start from scratch in a new workbook?  You already HAVE a PowerPivot workbook and want to just “port” the calendar chart into THAT workbook?

It’s easy.  Probably a 30 minute task, and that includes the time spent reading this post.

## Introducing… the Calendar Chart!

“CalChart” – The Most Absolutely Awesome Thing I’ve Ever Done in Excel
(Data:  Fake UFO Sightings/Alien Abductions – Data I 100% Made Up)

### New Chart Type Added to Excel 2010!

Yes, it’s a new chart type.  And yes, it’s been added to Excel 2010.  But not by my former colleagues at Microsoft.  This was done by me, after being inspired by another Excel pro, and with a heavy dose of formatting and sparkline assistance from another.

And it’s not some new fancy software addin or something like that.

It’s formulas.  In the normal Excel grid.

## Dynamic TopN Reports via Slicers, Part 3

Guest Post by Colin Banfield [LinkedIn]

After Rob posted Dynamic TopN Reports Using PowerPivot2!, I downloaded the workbook from the provided link to examine how his “tricks” were done. Shortly thereafter, I sent a message to Rob complementing the techniques he used, and mentioning the potential for using the techniques in other scenarios – especially those that include dynamic charts. I didn’t realize that I was setting myself up, because Rob asked if I’d be willing to write a post detailing one of these scenarios. For the sake of continuity, it makes sense to treat the scenario that I will be discussing as an extension of  Dynamic TopN Reports via Slicers, Part 2. You should have thoroughly read and understood that post before you continue here.

The additions that I have made to Rob’s TopN reports are as follows:

• Created BottomN measures for Customers
• Created TopN/BottomN measures for Products

Created TopN/BottomN charts for customers and products that react to slicer selections for TopN, By (selected measure), and Year-Month range.

The final result of these efforts is shown in Figure 1.

Figure 1 – TopNBottomN charts (Click figure for a wider view)

• ## MiniPost 2 of 2: Changing Slicer Fonts With Macros

In Tuesday’s post, I showed how WingDings and other symbolic fonts can be used on slicers for an interesting effect.

But there was a lot of manual work involved with changing slicer style settings in order to accomplish that – eight repetitive steps.

I’m back today to share a macro that makes that process much more painless.

### I’d Like to Thank the Academy…  of Macro Recording

I’ve said it before and I’ll say it forever – the ability to record macros in Excel is the single greatest reference on how to write macros of your own.  And I leaned heavily on that recording feature to write what I’m sharing here.

So even if you’re not a macro person, I recommend trying out macro recording.  There’s some “how to” info at the end of a post from March.

## Mini-Post 1 of 2: Sharing Creative-Font Workbooks

In Tuesday’s post, I showed how WingDings and other symbolic fonts can be used on slicers for an interesting effect.

Question is:  what happens when you send the resulting workbook to someone else, or publish it to SharePoint?

Here are the quick results of my investigation so far.