Using Cut and Insert Cut Cells is a slow way to rearrange rows. Podcast 1978 introduced Custom List for the Fill Handle. Sorting data into a special sequence is another benefit of custom lists.
Type the list into the correct sequence.
File, Options, Advanced, 83%, Edit Custom Lists, Import
Use the Sort dialog
In Sort Order, open the dropdown and choose Custom List
Interesting (?) that you can sort the list reverse after choosing
Thanks to @NeedForExcel for suggesting this tip
Type the list into the correct sequence.
File, Options, Advanced, 83%, Edit Custom Lists, Import
Use the Sort dialog
In Sort Order, open the dropdown and choose Custom List
Interesting (?) that you can sort the list reverse after choosing
Thanks to @NeedForExcel for suggesting this tip
Transcript of the video:
Learn Excel from MrExcel podcast, episode 1992 - Sort by Custom List!
So I'll be podcasting this entire book, go ahead click that "i" on the top-right hand corner and subscribe to the playlist!
Welcome back to MrExcel netcast, I'm Bill Jelen.
Today's question, this is from, truly from my past.
I worked for a company, we had three regions, east, central, and west, and they had to be sorted into that sequence, well, there's no way to do it!
If you sort A to Z Central comes first, if you sort Z to A, West comes first, so what I found I was constantly doing is sorting the data, cutting Central, of course it was hundreds of rows, not just this, and then, using Home, Insert, Insert Cut Cells, over and over and over, and yeah, I hear what you're screaming, I tried the A. East, B. Central, C. West, but that doesn't fly, right?
The VP of Sales, he doesn't want to see, you know, A West or C West because, it was just confusing.
So, even though that would have worked, it was not acceptable.
I actually went to the VP of Sales and said: "John, you would make my life tremendously easier if you would just rename the Central region." He's like "To what?" I said "I don't care!
Anything from F to V would be fine, maybe middle." And he just "Get out of here, are you crazy?" All right so here, boy, if I would have known this trick 20 years ago, life would have been so much easier.
Go to a blank section on the spreadsheet, type the list in the correct sequence, select that list, go into File, Excel Options, go to Advanced, it's a long list, go 83% of the way down, choose Edit Custom Lists.
And then, here's the list, East, Central, West, click Import, and now it understands East, Central West, as well as it understands Sunday, Monday, Tuesday.
But the beautiful thing is, I can now sort by that list.
Alright, so we'll sort the data by customer.
I'm going to choose the list, I'm going to go into the sort dialog, Sort On Region, Sort On Values, check this out, there's a third choice - Custom List, and I can choose East, Central, West.
Click OK, click OK.
And it now sorts into the correct sequence.
It's also a little bizarre, I don't know why anyone would ever want to do this but it's there.
If you click sort now that I've added East, Central, West, they also add the reverse, West, Central, East.
In this case it probably makes no sense at all, but I guess it's good to know that it's there.
Alright so that's tip #11 of "40 Of The Greatest Excel Tips of All Time", buy the book at the whole list!
I'll be podcasting, I'll take me six weeks to get all these episodes done.
You can have the whole thing right now, cheap 25$, buy the book!
Alright, Episode recap: So it was back in Episode 1978 we introduced the custom list, but back then it was for the fill handle, being able to sort the data into a special sequence is another benefit of custom lists.
Type the list into a correct sequence, select the list, File, Options, Advanced, 83%, Edit Custom Lists, Import, OK, OK!
Then use the sort dialog and sort order, open the drop-down and choose Custom List, hoose the right list - it's interesting that you can sort the list.
I don't know, is it interesting or not?
Hard to say.
Alright now, I asked people on Twitter for their favorite Excel tips, and it was NeedForExcel who suggested this tip.
Thanks to NeedForExcel or for this tip, and thanks to you for stopping by!
We'll see you next time for another netcast from MrExcel!
So I'll be podcasting this entire book, go ahead click that "i" on the top-right hand corner and subscribe to the playlist!
Welcome back to MrExcel netcast, I'm Bill Jelen.
Today's question, this is from, truly from my past.
I worked for a company, we had three regions, east, central, and west, and they had to be sorted into that sequence, well, there's no way to do it!
If you sort A to Z Central comes first, if you sort Z to A, West comes first, so what I found I was constantly doing is sorting the data, cutting Central, of course it was hundreds of rows, not just this, and then, using Home, Insert, Insert Cut Cells, over and over and over, and yeah, I hear what you're screaming, I tried the A. East, B. Central, C. West, but that doesn't fly, right?
The VP of Sales, he doesn't want to see, you know, A West or C West because, it was just confusing.
So, even though that would have worked, it was not acceptable.
I actually went to the VP of Sales and said: "John, you would make my life tremendously easier if you would just rename the Central region." He's like "To what?" I said "I don't care!
Anything from F to V would be fine, maybe middle." And he just "Get out of here, are you crazy?" All right so here, boy, if I would have known this trick 20 years ago, life would have been so much easier.
Go to a blank section on the spreadsheet, type the list in the correct sequence, select that list, go into File, Excel Options, go to Advanced, it's a long list, go 83% of the way down, choose Edit Custom Lists.
And then, here's the list, East, Central, West, click Import, and now it understands East, Central West, as well as it understands Sunday, Monday, Tuesday.
But the beautiful thing is, I can now sort by that list.
Alright, so we'll sort the data by customer.
I'm going to choose the list, I'm going to go into the sort dialog, Sort On Region, Sort On Values, check this out, there's a third choice - Custom List, and I can choose East, Central, West.
Click OK, click OK.
And it now sorts into the correct sequence.
It's also a little bizarre, I don't know why anyone would ever want to do this but it's there.
If you click sort now that I've added East, Central, West, they also add the reverse, West, Central, East.
In this case it probably makes no sense at all, but I guess it's good to know that it's there.
Alright so that's tip #11 of "40 Of The Greatest Excel Tips of All Time", buy the book at the whole list!
I'll be podcasting, I'll take me six weeks to get all these episodes done.
You can have the whole thing right now, cheap 25$, buy the book!
Alright, Episode recap: So it was back in Episode 1978 we introduced the custom list, but back then it was for the fill handle, being able to sort the data into a special sequence is another benefit of custom lists.
Type the list into a correct sequence, select the list, File, Options, Advanced, 83%, Edit Custom Lists, Import, OK, OK!
Then use the sort dialog and sort order, open the drop-down and choose Custom List, hoose the right list - it's interesting that you can sort the list.
I don't know, is it interesting or not?
Hard to say.
Alright now, I asked people on Twitter for their favorite Excel tips, and it was NeedForExcel who suggested this tip.
Thanks to NeedForExcel or for this tip, and thanks to you for stopping by!
We'll see you next time for another netcast from MrExcel!
