Sort a pivot table field using a custom list. This requires too many steps in PowerPivot.
Transcript of the video:
MrExcel podcast is sponsored by Easy-XL.
Pivot Table Data Crunching Chapter 10; Power Pivot Sorting.
Hey welcome back to the MrExcel netcast. I'm Bill Jelen.
You know, I wrote a bunch of books over the past year about Excel 2010 and as I'm writing each book and you get to a chapters area, well what, what would be the interesting topic in this chapter and it's funny this very topic came up, I think in the Power pivot book so it's one that just clearly hacks me off.
This does not work, I have a Pivot Table here, based on Power pivot, I'm gonna add in the month field and the months show up alphabetically.
Every Pivot Table that you or I have created based on excel data for the last 15 years, those months will respect the custom list but for whatever reason the Power pivot people didn't do this and it takes 8 clicks to fix it. I hate these 8 clicks.
All right, so we go to the row labels drop-down, we open that Click number 1: More Sort Options, Click number 2: We wanna show this Ascending.
Click number 3: By month but then we have to go to more options, as, Click number 4: uncheck Sort automatically, Click number 5 and say that we want the First key sort order to be: Jan, Feb, Mar, April, that's click number 6 and 7.
Click OK, that's 8. Oh! 9 clicks, click OK again in order to get the data to show up...
in the proper sequence.
I'm not quite sure why every Pivot Table has been able to do this but for some reason the Power pivot people didn't bother to hook it up, maybe it's because the data is overlap.
I don't know I'm sure there was a really good reason for it but it's those, those 9 clicks, are just 9 clicks that drive me great.
So, there you have it, how to get your days of the week or months of the year sort of...
Hey, I wanna thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
Pivot Table Data Crunching Chapter 10; Power Pivot Sorting.
Hey welcome back to the MrExcel netcast. I'm Bill Jelen.
You know, I wrote a bunch of books over the past year about Excel 2010 and as I'm writing each book and you get to a chapters area, well what, what would be the interesting topic in this chapter and it's funny this very topic came up, I think in the Power pivot book so it's one that just clearly hacks me off.
This does not work, I have a Pivot Table here, based on Power pivot, I'm gonna add in the month field and the months show up alphabetically.
Every Pivot Table that you or I have created based on excel data for the last 15 years, those months will respect the custom list but for whatever reason the Power pivot people didn't do this and it takes 8 clicks to fix it. I hate these 8 clicks.
All right, so we go to the row labels drop-down, we open that Click number 1: More Sort Options, Click number 2: We wanna show this Ascending.
Click number 3: By month but then we have to go to more options, as, Click number 4: uncheck Sort automatically, Click number 5 and say that we want the First key sort order to be: Jan, Feb, Mar, April, that's click number 6 and 7.
Click OK, that's 8. Oh! 9 clicks, click OK again in order to get the data to show up...
in the proper sequence.
I'm not quite sure why every Pivot Table has been able to do this but for some reason the Power pivot people didn't bother to hook it up, maybe it's because the data is overlap.
I don't know I'm sure there was a really good reason for it but it's those, those 9 clicks, are just 9 clicks that drive me great.
So, there you have it, how to get your days of the week or months of the year sort of...
Hey, I wanna thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
