Community Pick: Many members of our community have endorsed this article.
Editor's Choice: This article has been selected by our editors as an exceptional contribution.

Sorting months chronologically and not alphabetically in a Pivot Table report based on Power Pivot data

Gašper KamenšekExcel MVP
Here's our problem. When you create a Pivot Table in Excel, you can Group that field by month and the sort will be logical (January, February, …). But when you create a Pivot Table based on Power Pivot, the grouping does not work! So you have to get to the month names by a different road. We do this by a Format function in PowerPivot, but the problem is that when you put this field in a Pivot Table, it gets sorted alphabetically. This is logical since the values are text and have nothing to do with dates as far as that Pivot Table is concerned, but this is a problem since the months are not sorted chronologically. This article will tell you how to achieve that.
Let’s start with a simple table in Excel that has only two columns. One has Date values and the other has a number of visitors on that date. Now we would like to create a Pivot Table report to see how the number of visitors is spread through the months.

Case 1: Pivot Table report based on an Excel Table

First we create a Pivot Table based on an Excel Table
Image 1The Pivot Table will show the number of visitors by months. But to do this, since we only have Dates, we have to do Grouping by months on the Dates
Image 2And right away we get the desired result.
 Image 3

Case 2: Pivot Table report based on Power Pivot data

First we add our Table data to Power Pivot the easiest way – by using the Add to Data Model command on the PowerPivot tab.
Image 4Now that we have the data in the Power Pivot we can create a Pivot Table report from Power Pivot window. But when we create a Pivot Table and want to see the analysis by months we see we just can’t select the Group command. It is grayed out…
Image 5So to get to months we use a different trick, we go back to the Power Pivot window and create a calculated column using a Format function. This is a PowerPivot rendition of the Text function from Excel. The syntax is the same, just the names differ.
Image 6Excel Version
 Image 7Power Pivot version.
Using the Format function we now get the month names and a new field to create a Pivot Report by. But when we create it, it looks quite disappointing.
 Image 8So the numbers are OK, but the sorting is alphabetical and not the kind we want. To get the sorting right, we have to go back to the PowerPivot window and create a new calculated column using the Month function. This way we now get a month number along each date and month name.
Image 9Now just adding that to the Pivot Table report would get rid of our problem, but let’s not forget that we want the month names as they were, only the sorting is wrong. But now we have all we need.
 Image 10In the Power Pivot window, we select a value in the month name column and then select a Sort by Column command on the home tab and hey, look at that. You can now say that the Month name column will be sorted by Month No. column.
 Image 11Doing that has changed our Pivot Report instantly
 Image 12And we are one step closer to eternal happiness.
Gašper KamenšekExcel MVP

Comments (1)

Hi GasperK - Great stuff here for the PowerPivot, but on a totally unrelated subject to this posting, how did you manage to take snapshots of screens while actually clicking on a menu item. For example the screen capture of "Sort by Column"

Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.