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.

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

#### 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 6 Comments

1. For more about our Calendar Vis Project, there’s an updated PowerPivot demo version at https://insights.hostedpowerpivot.com/sites/TrafficSafety/Pages/default.aspx
We expect to be in “full production” with an expanded version soon.

THANK YOU again to Bill, John, Rob, Jeff, and Nan for your enthusiastic and creative interest – and thank you to everyone who commented on the earlier post!

Joni Graves
Department of Engineering Professional Development (EPD)
Wisconsin LTAP / Transportation Information Center
University of Wisconsin – Madison

1. David Hager says:

So, EPD means Engineering Professional Development, per the author of the workbook. I guess it’s not really a coincidence that it could also mean “Excel Pro Developer” or “Excellent PowerPivot Designer”, both of which apply to Rob and his partners.

1. David Hager says:

Bob Umlas and I would (in a friendly way) challenge your assertion of who is the king of array formulas.

1. King?

No doubt you guys rock.

I’m happy with “hero!”

1. David Hager says:

It’s a 90’s thing. Bob was acknowledged as the king of array formulas. I came along and learned from him and perhaps became his equal. However, I don’t think that either of us ever reached the level of innovative uses of array formulas that you have. Honestly, I think that Oscar at get-digital-help.com is the current “king” of array formulas. However, DAX formulas are like array formulas on steroids, and represent the future in data analysis.