Sorting is an Excel function that is painfully familiar and familiar to almost everyone. However, there are several non-standard and interesting cases of its use.
Case 1. Sort by meaning, not alphabetically
Imagine a very common situation: there is a table in which there is a column with the name of the month (January, February, March …) or the day of the week (Fri, Tue, Wed …). With a simple sort on this column, Excel arranges the items alphabetically (i.e. from A to Z):
And I would like, of course, to get the usual sequence from January to December or from Monday to Tuesday. This can be easily done with a special sorting by custom list (custom list sorting).
Select the table and press the big button Sorting tab Data (Data — Sort). A dialog box will open in which you need to specify the sort field (column) and select the sort type in the last drop-down list Custom List (Custom List):
After that, the following window will open, in which you can select the sequence of months or days of the week we need:
If the required list (for example, months, but in English) is not available, then it can be entered in the right field by selecting the option New list (New List):
You can use as separator comma or key Enter. Once you create such a custom list, you can use it in other Excel workbooks.
An interesting nuance is that in this way you can sort not stupidly alphabetically, but by significance and importance any hierarchical objects, and not just months or days of the week. For example:
- positions (director, deputy director, head of department, head of department…)
- military ranks (general, colonel, lieutenant colonel, major …)
- certifications (TOEFL, ITIL, MCP, MVP…)
- customers or goods according to your personal importance (whiskey, tequila, cognac, wine, beer, lemonade…)
- etc.
Case 2: Sort text and numbers at the same time
Suppose that our table has a column with codes for various parts and assemblies for cars (part number). Moreover, large assembled parts (for example, a gearbox, engine, steering) are indicated by a purely digital code, and the small parts that they include are indicated by a code with the addition of a clarifying number through, say, a dot. Trying to sort such a list in the usual way will lead to an undesirable result, because Excel sorts separately the numbers (numbers of large aggregates in the assembly) and separately the text (numbers of small parts with dots):
 |  |
And, of course, I would like to get a list where after each large unit its details will go:
To implement this, we need to temporarily add another column to our table, in which we turn all the codes into text using the TEXT function:
If you then sort by that column, Excel will ask you how to sort numbers and text:
If you select the second option in this dialog box, then Excel will not convert the numbers of large aggregates to numbers and will sort the entire list as text, which will give us the desired result. The auxiliary column can then, of course, be deleted.
- Sort by color
- Sort by color with the PLEX add-on
- Sort by formula