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:
After:
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.