“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.***
