+ Reply to Thread
Results 1 to 3 of 3

Can't Get Sort Order Correct in Pivot Table

  1. #1
    Registered User
    Join Date
    Orlando, Florida
    MS-Off Ver
    Professional Plus 2016

    Can't Get Sort Order Correct in Pivot Table

    I created a pivot table to compare monthly totals year over year. Each data record has a date (named moyr) which is always the first of the month (so that all of my data is grouped by month). My columns are moyr and Years (which Excel inserts). My Values are Sum of amount. This produces a pivot table with 3 columns for each month (last year, this year, both years' amounts added together). In my data query I multiply last year's numbers by -1 so that when the two years' amounts are summed, I get the net difference. I know that is clunky, but I didn't know how else to do it.

    So now the problem. I sort the columns from newest to oldest. This worked great until January. I should be seeing Jan 2019/2018 followed by Dec 2018/2017 followed by Nov 2018/2017 etc. But it is sorting by month only. So, I get Dec 2018/2017, Nov 2018/2017, etc and Jan 2019/2018 is last. Any help would be greatly appreciated. And I am totally open to reworking my data query if that's what is needed.

    Here is a screen shot of my pivot table. I hide the middle months so you can see both ends of the sort order.


  2. #2
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Excel for Windows & Mac - all versions.

    Re: Can't Get Sort Order Correct in Pivot Table

    Hi and welcome to the forum

    Are your dates proper date numbers?

    Please upload a workbook or a representative cut down copy, anonymised if necessary. It is always easier to advise if we can see your request in its context.

    Show a before and after situation with manually calculated results, explaining which information is data and which is results, and if it's not blindingly obvious how you have arrived at your results some explanatory notes as well.
    Richard Buttrey

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Forum Expert rorya's Avatar
    Join Date
    MS-Off Ver
    2016 primarily

    Re: Can't Get Sort Order Correct in Pivot Table

    It sounds like you need to not group the dates. Instead add a new calculated year column to your data source, then use the original date column as the first column field and format it as month only, with the new year field as the second column field.
    Sue, you're shouting at tea

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1