Join Curt Frye for an in-depth discussion in this video Creating a custom sort order, part of Office 365 for Mac: Excel Essential Training.
- Most sorting operations arrange data into numerical or alphabetical order. But what do you do if your data doesn't make sense if it's sorted into alphabetical order? Well, in that case, you can create a custom order. This workbook contains two data lists that are both organized in alphabetical order for one column, but it doesn't make sense. On the left, you can see that I have the months for the year 2014 arranged in alphabetical order, which of course, is not the correct order for where they fall in the year and the same thing for the seasons.
Over in the right section, I have Fall, Spring, Summer, and Winter, when in fact, those are not in the proper order. So how do I sort using a custom list? Well, it depends on whether the custom list is built in or whether it's one you have to make yourself. Months are, in fact, a custom list that comes with Excel, so you can just make the appropriate selections when you perform your sort. So I'll click cell B2, actually it's already selected, but you can click any cell in the B column in the left hand data list and then on the Home tab, click the Sort and Filter button and click Custom Sort.
And here you can see that I have a sort level, and I'm sorting by Month, which is correct, but I'm sorting on Values, which is correct, A to Z, which is not. That's what gave me this nonsensical alphabetical order in the first place. So I need to click the order list arrow and from the three items that appear, I click Custom List. Doing so displays the Custom Lists that are built into Excel or that you have added yourself. So you see here we have Sunday, Monday, Tuesday in both short and long formats.
The short format for the months and the long format for the months. So what I want to do is to click this bottom list: January, February, March, and so on and click OK to use that as my sorting criteria. And now when I click OK, you see that my list has been sorted into the appropriate order. So that's what you do if you're using one of the four lists that are built in. If you want to create your own, then you need to open the Excel Preferences dialog box. To do that, hold down the command key and type a comma.
That displays the Excel Preferences dialog box. You can also display that by opening the Excel menu and clicking Preferences. But from here look in the middle section and click Custom Lists. The Custom Lists dialog box lets you either use an existing list or create a new one. So I'll click New List and then click over in the List Entries box. I want to put the seasons into the proper chronological order so I'll type Spring, then return, Summer, return, Fall, return, Winter and then click Add.
Doing so displays the list, you see it has Spring, Summer, Fall, and Winter. If I want to delete a list after I've created it, then I can just click it and click Delete. If you're values are already available in your worksheet, then you can display the Custom Lists page of the Excel Preferences dialog box, then click in the Import List from Cells box, click the Collapsed Dialog button, select the cells and then click Import. But in this case, I'm done, so I can close the Excel Preferences dialog box.
Now I'll go to my data list on the right and I'll click any cell in the Season column, then again, on the Home tab, click the Sort and Filter button, click Custom Sort. So I'm sorting by and my column will be Season. I am sorting by Values but instead of A to Z, I'll click the list arrow and get a Custom List and I'll click Spring, Summer, Fall, and Winter, which is at the bottom. Click OK, everything looks good, click OK again and you see that I have Spring, for 2014 and 2015, Summer for 2014 and 2015 and so on.
So to edit this sort, I can go back in, click Custom Sort, now I'll add a new sorting level. So I'll click the Add Level button. I want to sort by Year, Values, Smallest to Largest, and I also want to change the order of the rules. So I will drag Sort By below Then By and you can see that I am now sorting by Year and the by Season. So I'll click OK and my list is now in what I wanted as my original order with the seasons in Spring, Summer, Fall, and Winter order and it's also ordered by year.
Creating a custom list lets you sort your data using orders that are specific to your business. Creating a list of seasons in the order Spring, Summer, Fall, and Winter helps you sort data by time with the added benefit of being able to extend the list using Auto Fill.
Author
Released
3/9/2018- Customizing the Ribbon
- Formatting worksheets, cells, and cell data
- Sorting and filtering data
- Working with formulas
- Detecting formula errors
- Creating charts
- Importing data
- Inserting objects and graphics
- Using PivotTables
- Recording macros
- Sharing workbooks
Skill Level Beginner
Duration
Views
Related Courses
-
Excel 2013 Essential Training
with Dennis Taylor6h 32m Appropriate for all -
Windows 10 Essential Training
with Nick Brazzi8h Appropriate for all -
Photoshop CC 2015 Essential Training
with Julieanne Kost12h 27m Beginner -
HTML Essential Training
with James Williamson5h 54m Beginner -
Git Essential Training
with Kevin Skoglund6h 25m Beginner -
Java 7 Essential Training
with David Gassner6h 4m Beginner
-
Introduction
-
Welcome51s
-
-
1. Getting Started with Excel
-
Getting help in Excel3m 11s
-
2. Managing Workbooks
-
Setting workbook properties2m 45s
-
3. Working with Worksheets, Cells, and Cell Data
-
Creating named ranges5m 58s
-
Creating an Excel table5m 9s
-
4. Sorting, Filtering, and Managing Worksheets
-
Sorting worksheet data3m 6s
-
Creating a custom sort order4m 49s
-
Filtering worksheet data3m 55s
-
Managing worksheets5m 4s
-
-
5. Summarizing Data Using Formulas and Functions
-
Adding a formula to a cell3m 56s
-
6. Analyzing Data and Formulas
-
Rounding cell values4m 14s
-
Managing scenarios7m 1s
-
7. Formatting Worksheet Elements
-
Managing text alignment4m 46s
-
Copying cell formats3m 29s
-
Managing cell styles4m 10s
-
Managing Office themes5m 45s
-
8. Working with Charts
-
Creating pie charts2m 25s
-
Creating line charts3m 11s
-
Creating XY (scatter) charts2m 38s
-
Creating stock charts2m 42s
-
Adding trendlines to charts3m 31s
-
Creating sparkline charts4m 17s
-
9. Working with External Data
-
Using hyperlinks4m 18s
-
10. Working with Objects
-
Adding and adjusting images4m 58s
-
Manipulating text boxes3m 20s
-
Creating SmartArt graphics4m 19s
-
Creating WordArt2m 55s
-
11. Exploring PivotTables
-
Applying a PivotTable style2m 26s
-
12. Reviewing and Sharing Your Spreadsheets
-
Checking spelling2m 55s
-
Managing workbook comments3m 29s
-
Exporting to other formats2m 48s
-
Protecting a workbook3m 23s
-
-
13. Automating Workbooks Using Macros
-
Running an existing macro4m 31s
-
Recording a macro2m 46s
-
Adding comments to a macro2m 23s
-
-
Conclusion
-
Next steps1m 13s
-
- 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.
CancelTake 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.
Share this video
Embed this video
Video: Creating a custom sort order