• Sorting A Column Of PowerPivot Data By Another Column

    One of the things that used to drive me crazy about working with PivotTables in PowerPivot’s initial (2008) release was summarizing dates by month. With a standard PivotTable, we can use the built in Group functionality to group dates by Years, Quarters and Months. But in PowerPivot, that functionality wasn't implemented. To deal with this, we have to provide our own date table, but the months never really sorted well, and we had to resort to tricks to coerce them into the right order.

    In short, I was never happy with my PivotTables when they ended up looking like this:

    Slicers had similar issues as well:

    Excel 2010 & PowerPivot 2008 Solution:

    To counter this, we pretty much had to add a column of data to our tables that converted our months into the mm-mmm format, which mean that they would show up as 01-Jan, 02-Feb, etc… Doing this would put the PivotTable columns and slicer buttons in the correct order.

    Excel 2010 & PowerPivot 2012/Excel 2013+ Solution:

    Fortunately, PowerPivot 2012 gave us a way to sort our columns so that they show up correctly in our PivotTables and slicers. Here’s how.

    Example 1: Sorting Months

    In the PivotTable pictured above, the “Month_Short” column is out of order. To fix this, we enter PowerPivot and pull up the table that has our dates:

    Next we:
    • Click somewhere in the “Month_Short” column
    • On the PowerPivot Home Tab, click “Sort by Column”

    Now we need to figure out which column to sort by. In this case, we want to sort by the “Month_Num” column. January will therefore sort as 1, February as 2, etc:

    At this point, we can click OK, go back to Excel and refresh our PivotTable:

    Much better! And it works for Slicers too:

    Example 2: Sorting Weekdays

    Now, what if we wanted to add weekdays to the mix? Can we have more than one sort per table? Of course!
    We’d head back in to PowerPivot, find the “Day_Short” column and choose to “Sort by Column” again:

    Now, the question is, which column do we sort our Day_Short column by? Logically, you’d think it would be Day_Num, as that has the two digit date you’re trying to sort, right?

    If you try that, however, Excel will throw an error for you:

    So what does that mean?

    Basically, what it means is that the relationship between the “Day_Short” and whatever it is being sorted by must be a one-to-one relationship. In the case of Day_Num, Excel is complaining because Sunday could be 28 (Jan), 3 (Feb), 10 (Feb), 17 (Feb), etc… Monday could be 29 (Jan), 4 (Feb), etc… In short, Excel has too many options to sort, so it can’t figure it out.

    What Excel is looking for is something very clear and consistent to work with. So how do we make it sort as follows?
    • 1 Sun
    • 2 Mon
    • 3 Tue
    • 4 Wed
    • 5 Thu
    • 6 Fri
    • 7 Sun

    Well… conveniently, these just happen to be the numbers should in the Weekday_Num column! Every Sunday has a corresponding value of 1, Monday = 2, etc… So let’s sort by that one.

    At this point we can return to Excel, Refresh the model, and drop the “Day_Short” onto the PivotTable. Notice that it also sorts correctly:

    Try this yourself:

    If you’d like to replicate the above:
    • You must be running Excel 2010 and PowerPivot 2012
    • Download the sample database
    • Create a new Excel file and open PowerPivot
    • Link to the following tables in the Access database:
      • tblDates
      • qryCOA
      • qryTransactions

    • Create the following relationships:
      • qryTransactions [TranDate] --> tblDates [Key_date]
      • qryTransactions [Link_AccDept] --> qryCOA [Link_AccDept]

    • Create a new PivotTable as follows:

    • Using the slicer, restrict your data to Dept 150 for 2009


    I'm afraid that you must be logged in to comment or leave a testimonial. I wish it could be otherwise, but I'm trying to keep my site spam free for everyone's benefit. If you don't yet have an account it's completely free to sign up, and a very quick process. Simply click here to Register. Not only can you post a comment here, but it gives you full access to posts questions in our forum as well!


    If you already have an account, and just haven't logged in yet, what are you waiting for? Login Now!

    Comments 1 Comment
    1. RFoster's Avatar
      RFoster -
      Hello - new here and somewhat new to the advanced features of MS Excel. I have begun using slicers for my leadership team and they are huge fans; however, I cannot figure out how to sort specific columns (e.g., sales, or units) from largest to smallest. I have seen dummy buttons with what looks like a macro attached, but not sure how to do that. Is it possible to create slicers that sort sales dollar columns or sales unit columns?
  • MVP Logo
  • Recent Forum Posts


    Complex counting help

    The table in the latest file needs refreshing - I should have done that myself - sorry....

    p45cal 2020-08-05, 05:45 PM Go to last post

    Complex counting help

    Hi p45cal - I think there is still some error in the logic but your suggestion of using Power Query has been invaluable. I have been researching that...

    Mr_E_Man 2020-08-05, 05:35 PM Go to last post

    Complex counting help

    Attached adjusted accordingly....

    p45cal 2020-08-04, 11:31 PM Go to last post

    Complex counting help

    Hi p45cal - the specific "DT" function is the only one that does not get counted for the keypad on the 2nd or 3rd reference, it is different...

    Mr_E_Man 2020-08-04, 05:55 PM Go to last post

    Complex counting help

    The attached contains a table at cell I6, which agrees with your totals except for the keypad count of the first site (1185) where I have counted the...

    p45cal 2020-08-04, 03:05 PM Go to last post