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

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

  1. Bill Jelen

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


    1. powerpivotpro

      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

    simply brillant!

    1. powerpivotpro

      Thanks Jeff! :)

  3. Dustin Harper

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

    1. powerpivotpro

      Glad to help. Most people’s Excel “addiction” starts with a single event, btw, so be careful :)

  4. johncon

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

    1. powerpivotpro

      See John Bradley’s comment – there is your warlock overlord :)

  5. John Bradley

    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!

    1. powerpivotpro

      This comment “wins.” :)

  6. Ben Niebuhr

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

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

      And Igor doing his thing, of course.

  7. David

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

    Simply awesome. Great job RC

  8. Jeff Elderton

    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

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

  10. Dan


  11. David Hager

    EPD – is that like an Excel Pro Developer?

  12. David Hager

    Or an excellent PowerPivot developer?

  13. Marco Russo

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

  14. rich soby

    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


    1. powerpivotpro

      Thanks Rich, an attempt at an explanation is coming tomorrow :)

  15. GeorgeWNYC

    An astounding display of virtuosity. Bravo !

  16. Joni Graves

    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

  17. Kris

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

  18. anderson jarman

    When will this be updated for Excel 2013

    1. powerpivotpro

      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?

  19. anderson jarman

    The excel does upgrade the powerpivot but every cell contains a #N/A

    1. Scott Stauffer

      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. Paul J. Keenan

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

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

        2. Mahit

          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. Paul J. Keenan

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

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

  20. rajesh


  21. Mubbasher

    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