Calendar Chart in Excel - PowerPivot Can Do Some Amazing Things

“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…

PowerPivot - Excel Calendar Chart Can Display Any Year, Any Month, Any Metric

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:

PowerPivot - Excel Calendar Chart Can Display Any Year, Any Month, Any Metric

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 Smile

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:

  1. PowerPivot (of course)
  2. Cube Formulas
  3. Conditional formatting
  4. Sparklines
  5. Array Formulas
  6. Named, relative, and absolute references
  7. 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.***

UPDATE:  Part Two – Adapting to Your Data in Thirty Minutes or Less

***Quickly adapt the CalChart for use with your data***

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 36 Comments

  1. 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

    1. 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! :)

  2. 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!

  3. 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”.

    1. Actually, there was enthusiastic fist pumping, shouting, and profanity.

      And Igor doing his thing, of course.

  4. Insert pic of Jess Ennis, flash some gold around and GB calendar done! Send to print…

    Simply awesome. Great job RC

  5. 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!

  6. 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

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

    1. 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?

    1. 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.

      1. The CUBE* functions require the name of the data model as a parameter. In previous versions this was called “PowerPivot Data”. In Excel 2013, this became “ThisWorkbookDataModel”, which broke the formulae in this workbook.

        To fix, do a global search and replace throughout the workbook with the former being replaced by the latter.

        1. Hi Paul, an update to my earlier comment : after doing the Global Find & Replace, the #N/A disappear but when I try to change any filter, Excel gives an error message asking to upgrade the data model with PowerPivot for Excel 2013.

          1. Hi Mahit, this is normal. Once Excel updates the data model for you, I seem to remember that it will ask you to restart Excel. Once this is done and the data model name is updated, it should work for you.

          2. Hi Paul, even after the global find/replace & a restart, Excel is asking the same upgrade of the data model.

  8. Hi everyone,
    i encounter the problem with moving model to my business model – i want it to adapt it somehow in my business case.
    It just simply doesnt allow it- saying something about object reference error. can anybody help with it? i appreciate

Leave a Comment or Question