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.
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.
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:
- The aforementioned Dany Hoter
- Chris Webb, with his posts [link removed due to 404] here and [link removed due to 404] here
- Darren Gosbell
- Hrvoje Piasevoli
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: