PowerPivot Calendar Chart in Excel: Specific Steps for Adapting it to Work With YOUR Data


Modifying This to Work With Your Existing Workbook Isn’t Hard


Given the continued popularity of the Calendar Chart and the post I did on its anatomy, I thought I’d continue today with a more pragmatic “how do I adapt this to work with my data?” post.

Adding the Calendar Chart to YOUR PowerPivot Workbook

OK, so you like the calendar chart but you don’t want to start from scratch in a new workbook?  You already HAVE a PowerPivot workbook and want to just “port” the calendar chart into THAT workbook?

It’s easy.  Probably a 30 minute task, and that includes the time spent reading this post.

Read the Rest

Explaining the PowerPivot Calendar Chart, Plus an Updated XLSX Download


Hidden Rows and Columns Visible, Color Coded, and Explained
(Slicers Deliberately Moved Aside for Clarity)
(Click for Larger Version)

A Most Popular Post Indeed!

Well the CalChart post was a hit – the second most popular post of this year in fact.  (Second only to Dan Battagin’s spreadsheet formatting post, and that one had the benefit of being directly linked to from the official Excel blog – Dan is a big cheater).

I particularly enjoy how many Excel Pros are arriving at this blog for the first time as a result of the CalChart – you know who you are!  You’re helpless against the luxuriant charms of the CalChart! 🙂

And you have to have PowerPivot for it to work, muhaha.  Resistance is futile.  Go download it from Microsoft now.  It’s free.

Modifying it to fit your needs

The workbook I made available for download last week included a bunch of unused “machinery” – formulas and cells that I created while I was experimenting with different techniques, but ended up not using in the final version.

Read the Rest

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.

Read the Rest

Creating a Cycle Plot PivotChart

Guest Post by Colin Banfield [LinkedIn]

A cycle chart is a chart designed to show business sales cycles in a manner that cannot be expressed using other standard charts. For instance, a cycle charts can show how monthly sales vary over several years, or how daily sales vary over several weeks. For a good introduction to cycle charts, see this excellent article by Naomi Robbins.

Figure 1 shows cycle PivotCharts using 3M+ rows of sales data from the Contoso database.



Figure 1 – Monthly sales over years and daily sales over weeks cycle plots (click figure to see an expanded image)

Read the Rest

Excel 5-Calendar Date Table

Guest Post by Colin Banfield [LinkedIn]


For some time, I have been looking around for a fairly complete date table in Excel for use with PowerPivot. If you are working with data derived from a data warehouse, a date table is perhaps the most common dimension table that exists in the warehouse. However, not every scenario involves working with a data warehouse directly, and I simply wanted a “portable” date table. I found very little online, the best perhaps being this Excel table offered by the Kimball group (the table has been expanded since I originally downloaded it). I could have modified the Kimball table for my particular needs, but I decided to create one from scratch.  Late last year, Rob posted an article titled the Ultimate Date Table, which is available from the Azure Marketplace. I considered using this table instead of the one I was building in Excel, but the “Ultimate Table” lacks fiscal periods. Much of the analysis work I do includes fiscal periods.

Read the Rest