Most sorting operations arrange data into numerical or alphabetical order. But what if your data doesn’t make sense when it’s sorted into alphabetical order? In that case, you can define a custom sort order.
- [Instructor] Most sorting operations arrange data into numerical or alphabetical order. But what if your data doesn't make sense when it's sorted into alphabetical order? In that case, you can identify a custom sort order. I'll demonstrate how to use a built-in custom list and also create your own to perform custom sorts in this movie. My sample file is the CustomSort workbook, and you can find it in the Chapter Two folder of the Exercise Files collection. This workbook contains a single worksheet and on it I have two data lists.
On the left, you'll see that I have monthly inquiries for a particular company and the month names are sorted in ascending alphabetical order from A to Z and of course, that is different from the order that we have in our calendars. Excel has a built-in list that we can use to sort that into recognizable order. Also, on the right side, I have a data list that has inquiries by Season, and you'll see that it goes Fall, Spring, Summer, Winter, so they're not in what we recognize as an order with Winter, Spring, and Summer, and Fall, or Spring, Summer, Fall, and Winter.
So I want to do is to apply a built-in custom list to the left data list and create a new one for the information on the right. I'll start by clicking in cell B2 just so that I'm somewhere in column B with the month names and then to sort I will on the Home tab, click the Sort & Filter button and then click Custom Sort. Here you see I already have a sort that has been added to the column, sorting Month based on Values and the order is A to Z.
Let's say that I want to sort using a custom list. For that I will click the Order control's down arrow. And then, from the three items that appear, I will click Custom List. The built-in custom lists that are currently available appear. You can see that we have weekdays, both the three-letter abbreviation as well as the full names of the days, and also months, both again with three-letter abbreviation and the full spelled-out names of the month. In this case, we want to work with the last list, so I will click that and click OK.
So we're sorting our values in that particular order, click OK. And you see that now we have the months in chronological order. If you don't have a built-in list, then you need to create one on your own. I'll click cell F2, although I don't need to do that yet. And then to enter the Excel Preferences dialog box, I will press Command + Comma. Then within Excel Preferences, I'll click Custom Lists, that's in the Formulas and Lists area.
And I get the interface where I can add a new list if I want to, and you see the existing list here on the right. I'll start by entering my list entries here on the left. I'll start with Spring. Spring, then Enter, Summer. Enter, or Return. Fall, Return, Winter. So there are my separate list entries, I'll click Add. And the custom list has been added to the right side.
If for any reason you want to remove a list, you can just click it and click Delete. You can also import list values from cells if you already have the data in your worksheet. To do that, just click in the Import list from cells box, and then if you need to, click the Ref Edit button, select the cells, and then click Import to bring the values in. In this case, I don't need to do that, but I wanted to let you know that that was possible. My list has been added, so I will close the Excel Preferences dialog box.
Now I can go through the same sorting procedure that I did before with cell F2 selected and if you didn't click it earlier, please do it now if you're following along. Go to Sort & Filter, Custom Sort. Right, I am sorting by the column, Season. Sorting on the values and the order. Click the down arrow, Custom List. And the last list is Spring, Summer, Fall, and Winter, so I'll click that, click OK, click OK again, and I have my list.
Now note that I have both Springs, both Summers, both Falls, and both Winters. So what I'd like to do is to add another sort level so it's Year and then Season. So I'd go back to Sort & Filter, click Custom Sort. My existing sort is already here. And then I will click the plus sign. With my new level, I can define it and don't worry that we'll be sorting by Season first, and then Year, we'll change that in a moment. So I will click the down arrow for Column, make it Year.
Values, Smallest to Largest, okay. We have Sort by, Season and then Year when we want it to be Year and then Season. So I'll drag the top rule below the second rule, so in dragging the Sort by beneath Then by, and their positions reverse, we have Year and then Season. I'll click OK, and now I get the sort order that I wanted, by Year and then by Season within each year.
- Creating workbooks
- Manipulating cell data
- Sorting, filtering, and managing worksheets
- Using core functions and formulas
- Formatting worksheet elements
- Creating and managing conditional formats
- Working with charts
- Adding images and shapes
- Working with PivotTables
- Exporting workbooks