Excel Unplugged

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

Here is our “problem”. When you create a Pivot Table in Excel that has a date field and is based on data in Excel, you can Group that field by month and the sort will be chronological (January, February, …). But when you create a Pivot Table based on Power Pivot, grouping does not work! (This can also happen if no Power Pivot is used). Within a Power Pivot based Pivot Table, there is no Pivot Table setting that will sort months chronologically. Sorting Months Chronologically will require you to do some extra work, but you also gain much more flexibility!

Suppose you find it easier to learn through video. In that case, you can find the video tutorial for sorting months chronologically and not alphabetically on my YouTube channel: Click Here to View Video on YouTube or watch the video embedded at the end of this post.

Notice that I put the word problem in the first sentence in double quotes. The reason being that it’s really not a problem. It’s one of those – “it’s not a bug it’s a feature” things. Also, this feature does enable you to do much more than you’re used to!

Sorting Months Chronologically (the short version)

To Sort Months Chronologically, you need a Power Pivot Data Model and a Date field. Next you should get Month names and Month numbers by using Formulas. Finally you can then use Sort By Column in Power Pivot. To get the month names from Dates we use the Format function in Power Pivot (it’s the Power Pivot equivalent of the Text function in Excel, both are explained further down this post), 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 not what we are used to when it comes to Pivot Tables. This article will tell you how to get the chronological sorting in Excel (Case 1) and how to do it in Power Pivot (Case 2).

For both cases, we will use a simple Excel Table. It contains only two columns. Date values are in one, 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.

First let’s do it in Excel

Case 1: Pivot Table report based on an Excel Table

Select any cell within a table and go to Insert/Pivot Table

Sorting Months Chronologically in Pivot Tables

The Pivot Table will show the number of visitors by months. But to do this, since we only have Dates, we have to Group Dates into Months. Right click on any Date and select Group.

Sorting Months Chronologically in Pivot Tables

And right away we get the desired result.

Sorting Months Chronologically in Pivot Tables

If that was not the case, we would get the right sort order by sorting those by Custom Lists. More Sort Options/More Options remove AutoSort and select First key of Sort Order

Now let’s take the same data, but create a Pivot Table based on Power Pivot.

Case 2: Pivot Table report based on Power Pivot data.

First we add our Table data to Power Pivot the easiest way we can, by using the Add to Data Model command on the Power Pivot tab.

Sorting Months Chronologically in Pivot Tables

This gets the data into Power Pivot. Next, let’s create a Pivot Table. Simply by choosing Pivot Table Report from the Power Pivot window. Recreating the “Excel” Pivot Table, we  want to group dates by months and we just can’t select the Group command. It is grayed out…

Sorting Months Chronologically in Pivot Tables

To get the month names, 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 Power Pivot version of the Excel’s Text function. The syntax is the same, just the names differ. Compare the functions bellow.

Sorting Months Chronologically in Pivot Tables

Excel

Sorting Months Chronologically in Pivot Tables

Power Pivot

Using the Format function we get the month names. This also gives us a new field in a Pivot Table. However, adding Month Name to Rows looks quite disappointing.

Sorting Months Chronologically in Pivot Tables

The numbers are OK, but the sorting is alphabetical. We want to be sorting months chronologically. To get the sorting right, we have to go back to the Power Pivot window and create a new Calculated column using the Month function. This gives us a Month Number along with Date and Month Name.

Sorting Months Chronologically in Pivot Tables

Now simply replacing the Month Names by Month Numbers in 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 with the correct sorting. Luckily at this point we have everything we need. Also, we are just one step away from eternal happiness.

Sorting Months Chronologically in Pivot Tables

In 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 Number column.

Sorting Months Chronologically in Pivot Tables

Doing that has changed our Pivot Report instantly! And that is how you go about sorting months chronologically!

Sorting Months Chronologically in Pivot Tables

Conclusion

Wrapping up, think about this scenario. You want to sort the Month Names so that the first month would be July and the last would be June. Fiscal year sort of thing. You just add an Index column where July is 1 and June 12. Next you choose to sort Column Names by this new index using the Sort By Column command and you’re done!

Here is a video from our YouTube Channel depicting this technique:

More about Power Pivot here and here. If you want to see all the differences between a Power Pivot based Pivot Table and a normal Excel Pivot Table, you can read about it here.