When an ordinary alphabetical or numerical sort in Microsoft Excel does not work, review the advanced sorting options or add a custom sort pattern.
Excel has several advanced classification options, but most of us never venture beyond ordinary alphabetic and numeric types. If you need more than a simple classification, see the advanced classification options in Excel. In this article, we will discuss several advanced options. Then, I will show you how to create a custom classification for those times when even the advanced settings are not suitable.
I am using Office 365 on a Windows 10 64-bit system, but you can apply these sorting techniques to earlier versions. Custom types are not compatible with the browser edition. You can use your own data or download the demo files .xlsx and .xls.
MORE INFORMATION: Prices and features of Office 365 Consumer
How to use the advanced sorting options in Excel
Once you've mastered the simple, alpha and numeric types, you might want to see the Advanced Sorting Options. You may never need them, but if you do, they are invaluable.
To find these options, click on the Data tab and then click on the Sort option in the Sort and filter group. Then, click on the Options button to start the dialog shown in Figure A . (You must select a range of values to access this setting).
Sort from top to bottom is the default, and you're familiar with it, so let's look at Sort from left to right. Figure C shows the result of changing this option to Sort from left to right. The result is the same that is case sensitive because I did not deactivate that option; it remains established until you deselect it (you must be careful with this small problem). On the contrary, the Sort option from left to right remains set until you run an order from top to bottom: Excel automatically changes it. When you use sorting from left to right, you will probably need to set the row in the Sort by control. If you receive an error, try again and check that option.
Other advanced sorting options include cell and font color and conditional formatting icons. You can also add classification layers to form subgroups. You probably don't use these advanced options frequently. None of these advanced options allow you to apply a custom sort order.
How to use the Excel custom classification function
The Excel custom classification function allows you to determine the order of classification because some data comes with a classification order that is not alphabetical or numerical: it is ordered by business rules . For example, a list of sizes (petite, small, medium, large and x-large) could be sorted alphabetically, but not sorted by size, from smallest to largest and vice versa. For this, we can create a custom classification as follows:
- Click on the File tab and select Options.
- Select Advanced in the left pane.
- In the General section, click Edit custom lists. You will see the integrated Excel lists: you probably already trust some and you may have never known that you were using a custom classification.
- In the List entry control, enter the list in sort order ( Figure D ): Small, small, medium, large, x-large. (If the list is long or already exists as natural data, you can specify the cell reference and click Import).
- Click Add and Excel will transfer the new custom classification to the custom lists on the left.
- Click OK twice.
To apply the custom soft, select the list you want to order. Then, click the Start tab, click Sort and filter in the Edit group and choose Custom Sort from the drop-down list. If there is data in adjacent columns, you will be asked to expand the selection, but do not: choose Continue with the current selection and click Sort. From the Order drop-down menu, choose Custom List ( Figure E ). Select the list of sizes shown in Figure F and click OK twice. Figure G shows the ordered list.
Figure F-19659053] excelsort "data-original =" https://tr2.cbsistatic.com/hub/i/r/2019/08/14/d154f3bc-4bfd-4927-bde2-2f34797dae75/resize/770x/bda5b5c28dbc9d677c31d48ebafb1d3e/excelsort-f jpg [1965990-48] ] excelsort-f.jpg “/> Figure G