Today Bill shows us how to sort a pivot table field using a custom list as this requires too many steps in PowerPivot.
...This is the podcast companion to "Pivot Table Data Crunching: Microsoft Excel 2010", by Bill Jelen a.k.a. MrExcel.
...This is the podcast companion to "Pivot Table Data Crunching: Microsoft Excel 2010", by Bill Jelen a.k.a. MrExcel.
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 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 going to 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 Powerpivot 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 one.
More sort options, click number two.
We want to show this ascending, click number three.
By month, but then we have to go to more options as click number four.
Uncheck, sort automatically, let's click number five.
And say that we want the first key sort order to be Jan, Feb, Mar, April, that's a click number six and seven, click [ ok ], that's 8, 9 clicks, click [ ok ], again in order to get the data to show up, 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 Powerpivot people didn't bother to hook it up.
Maybe it's because the data's overlap.
I don't know, I'm sure there was a really good reason for it.
But, it's those 9 clicks are just 9 clicks, that drive me great.
So, you have it.
How to get your days of the week or months of the year to sort into the proper sequence in Powerpivot.
Hey, I want to thank you for stopping by, we'll see you next time 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 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 going to 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 Powerpivot 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 one.
More sort options, click number two.
We want to show this ascending, click number three.
By month, but then we have to go to more options as click number four.
Uncheck, sort automatically, let's click number five.
And say that we want the first key sort order to be Jan, Feb, Mar, April, that's a click number six and seven, click [ ok ], that's 8, 9 clicks, click [ ok ], again in order to get the data to show up, 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 Powerpivot people didn't bother to hook it up.
Maybe it's because the data's overlap.
I don't know, I'm sure there was a really good reason for it.
But, it's those 9 clicks are just 9 clicks, that drive me great.
So, you have it.
How to get your days of the week or months of the year to sort into the proper sequence in Powerpivot.
Hey, I want to thank you for stopping by, we'll see you next time another netcast from MrExcel.
