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.
- [Narrator] 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. On the left of this worksheet, you'll see that I have a list of monthly inquiries data for a company, and you can also see that the months here in column B are sorted into alphabetical order, and of course April does comes before August, and August comes before December, but they're all out of order, and February should come before April.
If you want to sort a list of values by a custom list, then click any cell within the list, and then on the home tab with the ribbon go to sort and filter, click this button and then click custom sort. That displays the sort dialogue box, and from here you see that we're sorting by month on values and the order is A to Z. If you want to change the order, then you can click... the control underneath order, and then click custom list.
That displays the built-in custom list that are available to you, and in this case we want to use the bottom list, which is the month names fully spelled out. So I click that, click okay, click okay again in the sort dialogue box, and your data has been sorted. So if you ever see April August, and know that you want to sort, you can do that from here. Let's say that the... sequence you want is not a built-in custom list. For example on the right side of the worksheet, I have the number of inquiries broken down by season, and let's say that I want the sequence to be spring, summer, fall, winter.
I don't have a built-in sequence for that, so I need to build one. To do that I can go to the Excel preferences dialogue box. The keyboard shortcut for that is command comma, and you'll see in the middle section under formulas and lists that there is a custom list icon, click that. In the custom list dialogue box, you'll see all the lists that are available, and you can also add one for yourself. If you already have your list entered into a worksheet, then you can click the ref edit button next to import list from cells, and select the cells that contain the values.
We don't have it in this case, so I'll click the expand dialogue button, and we can type in our entries one at a time. So I will in the list entries box type spring then enter summer... enter fall, enter winter. When you enter values this way in a list, you type the value and then press enter to move it onto the next line. Once you have all the values correct, and please do verify spelling, you can click add, and your custom list appears in the list of available lists.
We're done here so I will click the close button to close the dialogue box, and now we can go through a similar operation that we did to sort by month. I have cell F2 selected, and that is within the column that I want to sort. On the home tab I'll go to sort and filter. Click the button, click custom sort. The first thing we need to do is identify the column by which we want to sort. So I'll click the sort by button, and click season. Then we will be sorting on values, and we can select a custom list.
So I'll click the order areas control, click custom list, and from here I will click spring summer fall winter, the list we've just created, click okay, okay again to apply it, and you can see that we have sorted by spring, summer, fall, and winter. If we wanted, we could add a second sorting level to sort by season and then by year. One last note about custom lists, any values in a column that are not included in a custom list are sorted to the bottom.
If you're missing data, there's a possibility of misspelling, so look for that data at the bottom of the sorted data list, and make any changes that you need.
- Creating workbooks
- Manipulating cell data
- Using core functions and formulas
- Formatting worksheet elements
- Creating PivotTables
- Collaborating with others
- Working with charts
- Adding images and shapes
- Saving workbooks
- Printing files
Skill Level Beginner
1. Get Started with Excel
2. Manage Workbooks
3. Work with Worksheets, Cells, and Cell Data
4. Sort, Filter, and Manage Worksheets
5. Summarize Data Using Formulas and Functions
6. Format Worksheet Elements
7. Work with Charts
8. Work with External Data and Objects
9. Explore PivotTables
10. Review and Share Your Spreadsheets
- Mark as unwatched
- Mark all as unwatched
Are you sure you want to mark all the videos in this course as unwatched?
This will not affect your course history, your reports, or your certificates of completion for this course.Cancel
Take notes with your new membership!
Type in the entry box, then click Enter to save your note.
1:30Press on any video thumbnail to jump immediately to the timecode shown.
Notes are saved with you account but can also be exported as plain text, MS Word, PDF, Google Doc, or Evernote.