Dan English's BI Blog

Welcome to my random thoughts in regards to Business Intelligence, databases, and other technologies

Sorting PowerPivot Labels Continued

Posted by denglishbi on September 14, 2010

I was reading the new PowerPivot book, Practical PowerPivot & DAX Formulas for Excel 2010, I purchased a few weeks back and I came across some new information (always a plus).  The information I discovered ties back into my posting I did a few months back in regards to sorting labels manually – Sorting PowerPivot Labels Manually.  The posting talked about sorting month labels so that they were not in alphabetical order, but in the standard Jan, Feb, Mar, etc. order.  Now what I come to find out is that instead of doing that manual work you can simply use the ‘Sort A to Z’ option instead.  Excel is smart enough to know that these are months and should be sorted in a particular order.  Let’s take a look at this:

Before (notice that the months are sorted alphabetically):


Select the ‘Sort A to Z’ option for the month label name:




Now everything is in the proper order.  Not exactly what you would have expected, but this is what we want to see.  Nice little hidden feature.  So what else can this work for…hmmm…what else is standard?  How about days of the week?  Let’s take a look at that:

Before (notice that the days of the week are sorted alphabetically):


After the ‘Sort A to Z’:


Shazaam!  Sweet.  Now if only you could do this in the Slicers that would be golden, but no such luck.  Plus this is only for the particular PivotTable that you performed this on, so you would need to continue to remember to sort the label names in other PivotTables.

Hope you like that little trick, I know I do.

4 Responses to “Sorting PowerPivot Labels Continued”

  1. […] There you go.  Just another option that you have for sorting items in your PowerPivot reports. UPDATE (11/22/2010) – I have another blog posting here in regards to sorting that you might find interesting as well – Sorting PowerPivot Labels Continued […]

  2. Michele said

    Seems like this would solve the problem highlighted in this Mr. Excel vid:

    • denglishbi said

      Yep, just need to click on the Sort A to Z option (so maybe 2 clicks). One would not think that it would not work, but it is a hidden gem. Just wish you could use this same functionality in the Slicers.

  3. […] Sorting PowerPivot Labels Continued […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: