PowerPivotPro

Hidden Rows and Columns Visible, Color Coded, and Explained
(Slicers Deliberately Moved Aside for Clarity)
(Click for Larger Version)

A Most Popular Post Indeed!

Well the CalChart post was a hit – the second most popular post of this year in fact.  (Second only to Dan Battagin’s spreadsheet formatting post, and that one had the benefit of being directly linked to from the official Excel blog – Dan is a big cheater).

I particularly enjoy how many Excel Pros are arriving at this blog for the first time as a result of the CalChart – you know who you are!  You’re helpless against the luxuriant charms of the CalChart!

And you have to have PowerPivot for it to work, muhaha.  Resistance is futile.  Go download it from Microsoft now.  It’s free.

Modifying it to fit your needs

The workbook I made available for download last week included a bunch of unused “machinery” – formulas and cells that I created while I was experimenting with different techniques, but ended up not using in the final version.

So here’s a link to a much cleaner version that will be easier to navigate and understand than the one from last week.  If you downloaded it already, I recommend grabbing this newer version.

Download the Newer Version of the XLSX

With this workbook as our new baseline, let me give you some tips on how to tailor it to your own needs.  I’ll go element by element while trying to keep this post to a reasonable length.

Anatomy of the Report Sheet

I think that first diagram is a lot more helpful than any wall of text I could throw at you.  If you unhide rows and columns, you can inspect the formulas and how they depend on each other.

Each CalGrid “takes orders” directly from two places:  the second yellow measure cell, and the orange header cells above each grid.  And those orange header cells take THEIR orders from the Blue and Green Year and Month axis cells:

Detail Diagram for a Single CalGrid

Further Behind the Scenes:  HiddenCalGrid Sheet

This Hidden 37-Position Calendar Grid Range
is the “DNA” of the Calendar Chart

This grid, on a hidden sheet and referenced via named range, is essentially the “master” template for all of the visible CalGrids.

37 days in a month?  No not really – think of numbers 1 to 37 as positions, not dates.  If a month starts on a Sunday, position 1 WILL be used, and it will be used for the first day of the month.  And the only time position 37 is EVER used is for 31-day months that start on a Saturday.

I don’t think the named range SixRowCalGrid is actually referenced anywhere.  More specifically, each row of the grid has its own named range:

Back on the report sheet, each CalGrid references these 6 named ranges.

To Be Continued

That’s enough for today, I’m actually posting this from the passenger seat of a family road trip.

But in the meantime, I suggest you check out the following topics if you are not familiar with them:

Cube Formulas – absolutely essential to the Calendar Chart, and used everywhere.

1. Post by Dick Moffat
2. Post by Dany “Welcome Back” Hoter
3. Post #1 by me
4. Post #2 by me

While we are on the topic of cube formulas, I’d like to thank the following people for their assistance in constructing the “MDX” used in some of the CUBESET formulas of this workbook:

1. The aforementioned Dany Hoter
2. Chris Webb, with his posts here and here
3. Darren Gosbell
4. Hrvoje Piasevoli

Array Formulas

Hey, What’s With the Curly Braces Around This Formula???
That’s an Array Formula

PivotTables are the primary indicator of an Excel Pro – if you use pivots, you’re who I am talking about when I use that term.

Array Formulas, however – well, array formulas are used more rarely than pivots.  Wall Street uses them extensively, but outside of that, most people I train on PowerPivot have never used array formulas.

I’m not the right person to teach you array formulas, as even I only dabble.  Every now and then I find them perfect for the problem I am solving.  But that’s about once per year

So for array formulas, try these two articles:

http://office.microsoft.com/en-us/excel-help/introducing-array-formulas-in-excel-HA001087290.aspx

http://www.cpearson.com/excel/ArrayFormulas.aspx

This entry was posted on Tuesday, August 14th, 2012 at 1:46 pm and is filed under Cube Formulas, Custom Calendars, Quick Tips & Tricks, Time Intelligence, Usability and Appearance. You can follow any responses to this entry through the RSS 2.0 feed. You can skip to the end and leave a response. Pinging is currently not allowed.