skip to Main Content

Thats not a PowerPivot Report

“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=http://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

  1. PowerPivot-Backed – this report is indeed a PowerPivot report.
  2. No PivotTables anywhere – that’s right, there isn’t a pivot in the entire workbook!
  3. Custom formatting – I split the report body with horizontal and vertical dividers, and could place them wherever I wanted.
  4. Parameterization – users can play “what if” by changing input cells.  A numeric column in the report responds to those changes in realtime.
  5. Sorting – the report is dynamically sorted by that numeric column in #2.  Change a parameter, and the report re-calcs AND re-sorts.
  6. 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 🙂

Next Football Post:  Introducing Cube Functions >>

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 10 Comments
  1. I am guessing that you used “Convert to Formulas” to get the extra blank lines in there. But…how did you get those parameters to factor into the data without a refresh. Intrigue. I love it.

  2. […] more:  Not your average PowerPivot report « PowerPivotPro By pivot | category: pivot, pivot table | tags: another-sheet, bet-most, excel, jorge, […]

  3. You know…I went out and tried using Drawing Tools | Format ribbon to change the fill of the bounding box around the slicers.

    I could get it to fill with a color, but as soon as you click away from the pivot table, that bounding box disappears.

    So now I am thinking that you got the box around the slicers just by using cell fill and borders.

    I REALIZE that the colored box behind the slicers is nowhere near the most amazing thing here, but that’s the one thing I had to go try out…

  4. Not since Baywatch have so many been so teased…

    Nice work. Historically I have had to do this with Xcelsius, which has its own buffet of limitations that PowerPivot seems to be dismantling with ease.

Leave a Comment or Question