“THAT’s not a PivotTable! Oh wait…”
-Bill Gates
(OK, so I turned it into a movie quote of sorts by using the picture of Anthony Michael Hall playing Bill Gates in “Pirates of Silicon Valley.” Was that a good casting decision? I’m torn between “yeah it was OK I guess” and “well it WAS made for TV.”)
Setting that aside, the real Bill Gates DID in fact say that when we first showed him the new PivotTables in Excel 2007. Prior to that, the appearance of a finished PivotTable was something that the average user found off-putting and geeky. So we were pretty happy with that response to the new look.
PivotTables (and PivotCharts) have come a long way. A few clicks and you’ve got a very professional and customized report. But there are, of course, still cases where you need an even greater level of control than what pivots can give you. The good news is that if you find yourself in one of those cases, you still have other options.
Excel Pros – Do NOT tune out!
Excel pros are probably thinking “yeah, Rob, I know all about those other options. You make a PivotTable, you hide it on another sheet, and then build a report sheet using formulas, etc.”
I have a trick up my sleeve today, though. I bet most Excel pros have NOT seen what I am about to show you 🙂
Here it is – Not Your Average PowerPivot Report!
[youtube=https://www.youtube.com/watch?v=fQNhuF5jNnE&hd=1&w=640&h=480]
(You’ll need to crank your volume up – I got a new mic and need to adjust its sensitivity).
Summary of what’s in that report
- PowerPivot-Backed – this report is indeed a PowerPivot report.
- No PivotTables anywhere – that’s right, there isn’t a pivot in the entire workbook!
- Custom formatting – I split the report body with horizontal and vertical dividers, and could place them wherever I wanted.
- Parameterization – users can play “what if” by changing input cells. A numeric column in the report responds to those changes in realtime.
- Sorting – the report is dynamically sorted by that numeric column in #2. Change a parameter, and the report re-calcs AND re-sorts.
- Respects slicers – all columns in the report respect the slicers on the page as well. This also impacts the sort order of the report.
How it was done
I’m going to go through it step-by-step in the next few posts. Yes, I am deliberately teasing you 🙂
