One Training Session, Two (Three?) Blog Posts Inspired
Every now and then, I find myself training someone who I just KNOW is going to be teaching ME things in the near future. The questions they ask are the dead giveaways. And the next time we meet, they will be Darth Vader, telling my Ben Kenobi about how the circuit is complete.
I had the pleasure of teaching someone like this quite recently. He forced me to write some pretty nifty calcs, or at least some that *I* think are nifty.
We don’t even know what it is yet. We don’t know what it is. We don’t know what it can be, we don’t know what it will be, we know that it is cool.
Zuckerberg’s early assessment of Facebook was a lot like how I felt after first discovering PowerPivot 3+ years ago. I knew it was cool but had no idea how it would fit into an enterprise business intelligence environment. For a long time PowerPivot for me was just a cool thing that I used for my own data analysis or for proto-typing tools that I would eventually turn into “enterprise-level” solutions. Today I need a pretty compelling reason not to use PowerPivot for almost all of my organization’s analytic requirements. So where does PowerPivot fit into the enterprise BI environment and how do you get associates engaged and use it to provide value?
First: Understand that PowerPivot is Kinda Two Things
Let’s rewind all the way back to Office 2010, a world in which PV does not exist. (For most of you, we call this time Today. And for those stuck on 2007 or 2003, you may refer to this as Tomorrow. Or maybe even the Day After Tomorrow).
In that world, which is where this blog largely lives, it’s helpful to reflect that PowerPivot has two parts: the PowerPivot window, and the Excel window. They have the following relationship:
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.
Has THIS ever happened to you? We’re not even out of the Aarons yet.
Simple and Slick
Yesterday I saw Ken Puls post a really simple and effective trick – intentionally introducing a BLANK() measure in your pivot just to add a spacer column or row – and my response was “simple and slick, I like it.”
Well today I’m in Michigan, conducting some PowerPivot training/consulting, and that makes today a great day for a similarly slick and simple trick. Because hey, I’m not even here right now. Who’s writing this post??
Over in the Customers table in the PowerPivot window, add a simple calc column:
Let’s say you have three slicers: Percentile, Quarter, and Category:
For Bikes, You Always Want to See the 70th Pctile in Q2
And you find yourself selecting the same combinations over and over again. Above, you selected 70, 2, and Bikes – because Quarter 2 is essential for Bike sales, and 70 is the percentile you are most interested in for bikes. (Note that this percentile drives conditional formatting in this example – see this post for details).
When you look at Accessories, you always want to see Q1 instead, and set the Percentile threshold to 60. And for Clothing, it’s a different set as well:
But for Accessories, You Always Want to See the 60th Pctile in Q1
And for Clothing, It’s 80 and Q4 That You Always Want to See
Is that too repetitive for you? Three clicks with a brief pause in between each makes you feel like a monkey? There’s no satisfying you, is there? Sheesh
But hey, there’s a way to essentially set multiple slicers in one click, without VBA, as long as you know ahead of time what the desired combinations are.
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
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.
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.