Sometimes, your company tradition will force you to sort into neither ascending or descending sequence. Episode 520 shows you how to set up a custom sort in Excel 2003 or Excel 2007.
This blog is the video podcast companion to the book, Learn Excel from MrExcel and Excel 2007 Miracles Made Easy. Download a new two minute video every workday to learn one of the tips from the books!
This blog is the video podcast companion to the book, Learn Excel from MrExcel and Excel 2007 Miracles Made Easy. Download a new two minute video every workday to learn one of the tips from the books!
Transcript of the video:
Hey, welcome back to the MrExcel netcast.
I'm Bill Jelen.
Now, in yesterday's netcast we talked about how to do a special kind of sort.
where we had a sort on the center Portion of a serial number.
Reminded me of a cool trick that we have where we need to do something called a custom sort.
Here I have a series of products it runs from model 600 up through 900 and then there's also brand new model the new super duper model 1100 and when I sort this data set ascending the model 1100 because this is stored as text comes to the top instead of showing up at the bottom.
Now, the solution to this is to use a custom list I came over here to the right hand side and typed the list in the order that I wanted to actually appear and we go to Tools, Options and then the Custom List tab and click Import.
Now, because I've already pre-selected the data. It's going to work fine.
We have a new custom list now that shows that order in Excel 2003.
We'll go to the Data, Sort dialog say that we're going to Sort by Model, but then click options instead of saying that it's a normal sort order we want to say that we're going to sort it into model 600, 700, 800 order, click OK, click OK.
And now when we sort the 600's come to the top I'll hit end down and you'll see the 1100 to go to the bottom.
Now, in excel 2007 of course this has changed just a tiny bit to get the custom list in you're going to go to the office icon and then choose Excel Options and then right in the popular tab there's something called edit custom lists which basically brings us back to the same dialog box we had in Excel 2003, click Import and OK.
To do the sort you're going to go to the data tab of the ribbon and actually click on the sort icon instead of A to Z we're going to Sort by Model and then go to the order drop-down and choose custom list.
So, that we want to use the 600, 700, 800 sequence and now, when we click OK again, the models show up with the 600 at the top and the 1100 the bottom great way to do a custom sort anytime you have a unique list of items. For example, I used to have to sort East central and west with East coming first Central second and West third.
Easy to set up a custom list showing that sequence and then use a custom sort to force Excel to put your data in the sequence that your company normally presents it.
Hey, thanks for stopping by.
We'll see you next time for another netcast from MrExcel.
I'm Bill Jelen.
Now, in yesterday's netcast we talked about how to do a special kind of sort.
where we had a sort on the center Portion of a serial number.
Reminded me of a cool trick that we have where we need to do something called a custom sort.
Here I have a series of products it runs from model 600 up through 900 and then there's also brand new model the new super duper model 1100 and when I sort this data set ascending the model 1100 because this is stored as text comes to the top instead of showing up at the bottom.
Now, the solution to this is to use a custom list I came over here to the right hand side and typed the list in the order that I wanted to actually appear and we go to Tools, Options and then the Custom List tab and click Import.
Now, because I've already pre-selected the data. It's going to work fine.
We have a new custom list now that shows that order in Excel 2003.
We'll go to the Data, Sort dialog say that we're going to Sort by Model, but then click options instead of saying that it's a normal sort order we want to say that we're going to sort it into model 600, 700, 800 order, click OK, click OK.
And now when we sort the 600's come to the top I'll hit end down and you'll see the 1100 to go to the bottom.
Now, in excel 2007 of course this has changed just a tiny bit to get the custom list in you're going to go to the office icon and then choose Excel Options and then right in the popular tab there's something called edit custom lists which basically brings us back to the same dialog box we had in Excel 2003, click Import and OK.
To do the sort you're going to go to the data tab of the ribbon and actually click on the sort icon instead of A to Z we're going to Sort by Model and then go to the order drop-down and choose custom list.
So, that we want to use the 600, 700, 800 sequence and now, when we click OK again, the models show up with the 600 at the top and the 1100 the bottom great way to do a custom sort anytime you have a unique list of items. For example, I used to have to sort East central and west with East coming first Central second and West third.
Easy to set up a custom list showing that sequence and then use a custom sort to force Excel to put your data in the sequence that your company normally presents it.
Hey, thanks for stopping by.
We'll see you next time for another netcast from MrExcel.
