“CalChart” – The Most Absolutely Awesome Thing I’ve Ever Done in Excel
(Data: Fake UFO Sightings/Alien Abductions – Data I 100% Made Up)
New Chart Type Added to Excel 2010!
Yes, it’s a new chart type. And yes, it’s been added to Excel 2010. But not by my former colleagues at Microsoft. This was done by me, after being inspired by another Excel pro, and with a heavy dose of formatting and sparkline assistance from another.
And it’s not some new fancy software addin or something like that.
It’s formulas. In the normal Excel grid.
What’s so special about it?
The really nifty thing is that I did NOT manually enter calendar months into the grid. This is all driven off of a PowerPivot date table.
In other words, this visualization responds to slicers! I can change the measure displayed…
Changed from Total Abductions Measure to Late Night Sightings with a Slicer Click!
And I can also change the date range displayed. Don’t want to see 2001-2003, July-Dec? No worries, just clicky:
Now We’re Seeing 2009-2011, Just March-May!
In fact, I could change my calendar table to span the years of, say, World War Two and it would just work. There is nothing special about the years and months displayed above.
Try it Out!
If you want to see the CalChart in action, just click here and an interactive version will open in your browser:
https://insights.hostedpowerpivot.com/sites/Demo/Pages/CalChart.aspx
No, Really. Try it out 
Click that link above. It won’t install anything, download anything, etc. In fact you don’t even need Excel installed to try it out.
How Did I Do It?
I’ll have to explain in detail on Tuesday, but it uses a BUNCH of Excel features, all wired together in the way only can Excel can do things. There was, in fact, a bunch of boiling cauldrons and bubbling test tubes in the background while I created this:
- PowerPivot (of course)
- Cube Formulas
- Conditional formatting
- Sparklines
- Array Formulas
- Named, relative, and absolute references
- And I used macros to help me populate the grid (rather than manually typing formulas forever)
Don’t want to wait? Well, take a look for yourself…
Download the workbook here. (UPDATED August 14, 2012)
UPDATE: Part One of the Explanation
***Check out this post for a peek behind the scenes of this workbook.***
Rob – this is the most badass thing I’ve ever seen!
(I originally said “this is the most badass thing I’ve ever seen from you”, but I decided on the edit above).
Bill
Why thank you Bill
In many ways I can now call my life a success. To own, even temporarily, the title of “most badass thing ever seen by Mr. Excel” is well, quite fulfilling
You were part of the chain of events that inspired it, which is quite fitting I think. We are ALL always building on everyone else’s inspirations.
I even got help from the SSAS crowd to help me with the MDX in the cube formulas. This is a community project. It’s only fair that I “open source” the workbook – download away!
simply brillant!
Thanks Jeff!
This is amazing. I’m not a huge Excel user, but this is amazing. Definitely something I can use. Thank you.
Inspiring me to dig in to Excel deeper. 
Glad to help. Most people’s Excel “addiction” starts with a single event, btw, so be careful
I, for one, welcome our new Warlock overlord, and his “badass” chart.
See John Bradley’s comment – there is your warlock overlord
Wow, I have showed my underlings this and told them the first one to recreate it in a meaningful way that I can use gets a popcicle. Freaking awesome!
This comment “wins.”
As I said the other day, Excel is only as limited as your imagination. And your imagination doesn’t seem to be limited at all.
I hope when you got it working you jumped up and yelled “It’s alive! Aliiiive!” Then Igor joined you in a hearty, steeple fingered, “MUA HA HA”.
Actually, there was enthusiastic fist pumping, shouting, and profanity.
And Igor doing his thing, of course.
Insert pic of Jess Ennis, flash some gold around and GB calendar done! Send to print…
Simply awesome. Great job RC
As the formatting schlub behind Rob’s brilliance and the MDX community contribution, I can tell you that this moment punctuated for me the irresistible nature of Excel. Nowhere else does the canvas exist to encourage business users to take control of their analytical destiny. I love watching this community of like-minded business professionals explore the boundaries without the limitations of traditional IT constraints. Personally, I would love to see what you all DO with this template… Just because I am so impressed with what you do with PowerPivot!
Rob…you speak of the one (PowerPivot) who will bring balance to force …and throw couple of BI Tools off balance
Amazing
EPD – is that like an Excel Pro Developer?
Or an excellent PowerPivot developer?
Very nice – this is a new milestone in Excel dashboard capabilities!
WOW, I mean Wow – I see this showing up on all the excel sites in short order – this is a paradigm shift for what can be done
Now if I can just figure out how to utilize it or hope you have an explanation (format your data so – insert here and walla-walla – amaze your boss
Thanks for the great work
Rich
Thanks Rich, an attempt at an explanation is coming tomorrow
An astounding display of virtuosity. Bravo !
For the backstory on our Calendar Vis Project, you can see a PDF of the original, which was done with Excel 10 using pivot tables, at http://wisconsinsafetydataportal.org/default/assets/File/wi1.pdf
THANK YOU to Bill, aka MrExcel, and to John, Rob, Jeff, and Nan at Pivotstream for your enthusiastic and creative interest – this is a very exciting project!
Joni Graves
Department of Engineering Professional Development (EPD)
Wisconsin LTAP / Transportation Information Center
University of Wisconsin – Madison
Brilliant, Rob, really!!
Thank you for sharing!
When will this be updated for Excel 2013
I have not tried it yet in Excel 2013. Can you tell me what happens? I was hoping it would prompt you to upgrade. In Excel 2013, do you have the PowerPivot addin active?
The excel does upgrade the powerpivot but every cell contains a #N/A
Yes, this is what I’ve experienced too! A colleague of mine said that Rob demoed this at the PASS Business Analytic’s Conference in Chicago earlier this month. I will have to have a closer look later tonight. Looks very interesting.
Nice
Awesome, Micro Strategy demos show this type of Dashboard…. now we can show Micro Strategy guys how excel / power pivots rule the world.