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!

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!

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.

Comments 27

  1. Terry Lee says:

    Your article is really good to read. Amazing!!
    Thanks for sharing, could I post it on my Facebook to share with my friends?

    1. Thanks for the compliments. You can, the link is at the bottom of the article… Just click the Facebook button.

  2. lainaa 1000e says:

    Howdy! I just wish to give you a huge thumbs up for the excellent info you have here on this post.
    I’ll be coming back to your blog for more soon.

  3. sytropin says:

    Hello, I enjoy reading through your article.
    I wanted to write a little comment to support you.

  4. lainaa heti says:

    This article is in fact a good one it helps new net visitors, who are wishing in favor of blogging.

  5. Very good information. Lucky me I ran across your site by chance (stumbleupon).
    I’ve book marked it for later!

  6. donna mills says:

    This paragraph will assist the internet viewers for creating new blog or even a
    weblog from start to end.

  7. Spot on with this write-up, I actually think this website needs
    a great deal more attention. I’ll probably be back again to see more, thanks for the advice!

  8. laina says:

    Please let me know if you’re looking for a article writer for your
    blog. You have some really great articles and I believe I would be a
    good asset. If you ever want to take some of the load off,
    I’d love to write some articles for your blog in exchange for a link back to mine.
    Please send me an email if interested. Cheers!

    1. Hi,
      as a matter of fact, I would give great praise if you were to contribute to Excel Unplugged and if approved you can link to your blog as much as you like but overall there would be some conditions.
      – As I don’t know you and your writing style, in the beginning your contributions would have to be approved by me (just until the trust is established).
      – since the blog is Excel Unplugged all contributions would have to be Excel related and at least intermediate by level.
      – If this sounds good to you and to anyone else, you can reach me through the contact portion on my blog. You can also give your blog in the website field, so I can check it out…

  9. I do not know whether it’s just me or if everyone else experiencing problems with your site.

    It appears like some of the written text in your posts are running off
    the screen. Can someone else please comment and let me
    know if this is happening to them as well? This might be a issue with my web browser because I’ve had this happen before.

    Thank you

    1. Since this is the first mention of overflow on this site, the conclusion has to be that it’s a problem with your browser. Try a different browser and let me know if it still doesn’t work.

  10. Thanks in favor of sharing such a pleasant opinion, post is pleasant, thats
    why i have read it completely

  11. I must thank you for the efforts you have put in writing this
    website. I’m hoping to see the same high-grade content by you in the future as well.

    In truth, your creative writing abilities has inspired me to get my own site now 😉

  12. hello there and thank you for your info – I’ve certainly picked up anything new from right
    here. I did however expertise some technical points using this web
    site, as I experienced to reload the web site lots of times previous to I could get it to load properly.
    I had been wondering if your hosting is OK? Not that I am complaining,
    but sluggish loading instances times will very frequently affect your placement in google and could damage your
    quality score if ads and marketing with Adwords.
    Anyway I am adding this RSS to my e-mail and can look out
    for a lot more of your respective interesting content.
    Ensure that you update this again very soon.

  13. We stumbled over here coming from a different web address and thought I should check things out.

    I like what I see so now i’m following you. Look forward to going over your web page for a second time.

  14. Hello there, You have done an excellent job.
    I will certainly digg it and personally recommend to my friends.
    I’m confident they will be benefited from this website.

  15. After going over a handful of the articles on your blog, I really like your technique of writing a blog.
    I book marked it to my bookmark site list and will be checking back in the near future.
    Please check out my web site too and let me know what you think.

  16. Bryant says:

    Pretty! This was a really wonderful article. Many thanks
    for providing this info.

  17. Velda says:

    Thanks in support of sharing such a good idea, article is nice, thats why
    i have read it fully

  18. Aracelis says:

    Very good written post. It will be beneficial to anyone who
    usess it, as well as me. Keep up the good work – looking
    forward to more posts.

  19. Ramesh says:

    Thanks Gasper. You have saved my day.

  20. Manfred van der Sluijs says:

    Hi Gasper,

    Thanks for the tips and tricks!

    With respect to this article I have one additional question. What if I want to work with multiple years. How can I make sure that the value for January 2016 come after December 2015 and is not added to January 2015. I want to look 12 months back but with a dataset which contains multiple years I end up with a chart where January from one year is added up to the January of a previous year.

    I hope you have a solution for this.

    Thanks in advance

    1. Hi Manfred,

      your best option would be to create a calculated field YYYYMM so 201609 that would relate to September 2016… Or any other format you like, but it has to include month and year information.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: