Introducing… the Calendar Chart!

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:

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

35 Responses to Introducing… the Calendar Chart!

  1. Bill Jelen says:

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


    • powerpivotpro says:

      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. Jeff says:

    simply brillant!

  3. 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. :)

  4. johncon says:

    I, for one, welcome our new Warlock overlord, and his “badass” chart.

  5. John Bradley says:

    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!

  6. Ben Niebuhr says:

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

    • powerpivotpro says:

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

      And Igor doing his thing, of course.

  7. David says:

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

    Simply awesome. Great job RC

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

  9. sam says:

    Rob…you speak of the one (PowerPivot) who will bring balance to force …and throw couple of BI Tools off balance :-)

  10. David Hager says:

    EPD – is that like an Excel Pro Developer?

  11. David Hager says:

    Or an excellent PowerPivot developer?

  12. Marco Russo says:

    Very nice – this is a new milestone in Excel dashboard capabilities! :)

  13. rich soby says:

    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


  14. GeorgeWNYC says:

    An astounding display of virtuosity. Bravo !

  15. Joni Graves says:

    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

    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

  16. Kris says:

    Brilliant, Rob, really!!
    Thank you for sharing!

  17. anderson jarman says:

    When will this be updated for Excel 2013

    • powerpivotpro says:

      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?

  18. anderson jarman says:

    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.

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

        • Mahit says:

          Thank you for the fix, I was trying to figure out how to make it work in Excel 2013 :-)

        • Mahit says:

          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.

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

          • Mahit says:

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

  19. Mubbasher says:

    Awesome, Micro Strategy demos show this type of Dashboard…. now we can show Micro Strategy guys how excel / power pivots rule the world.

Leave a Comment or Question