Excel Unplugged

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


Here is our problem. When you create a Pivot Table in Excel that has a date field, 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! (This can also happen if no Power Pivot is used). So you have to get to the month names from month numbers and then a correct sorting by using. We get the month names from month numbers by using the Format function in PowerPivot (the PowerPivot 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 a problem since the months are not sorted chronologically. This article will tell you how to achieve that in both cases.

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

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 do Grouping by months on the Dates

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.

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.

Sorting Months Chronologically in Pivot Tables

Now 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…

Sorting Months Chronologically in Pivot Tables

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

Sorting Months Chronologically in Pivot Tables

Excel Version

Sorting Months Chronologically in Pivot Tables

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

Sorting Months Chronologically in Pivot Tables

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

Sorting Months Chronologically in Pivot Tables

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

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 No. column.

Sorting Months Chronologically in Pivot Tables

Doing that has changed our Pivot Report instantly!

Sorting Months Chronologically in Pivot Tables

And we are one step closer to eternal happiness.

More about Power Pivot here and here. If zou 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 *

%d bloggers like this: