Custom Sort Order in Excel

Microsoft Excel Worksheet is widely used for office, personal and various usages. The data present in the worksheet is a combination of numbers and alphabets. If the worksheet contains more content, organizing the worksheet is more critical for a good and easy viewing of data. To manage the worksheet, Excel provides a default function called SORT, which is used to sort the data based on user preference. The content or data in the worksheet is sorted based on alphabetic, numeric and anyways. The Sorting process in Excel is done in multiple ways, like, sorting data, sorting range, and sorting a sheet and a cell.

What is Custom Sorting?

Excel provides default sorting option. But sometimes, the user needs to sort the data based on their preference. To sort the order based on the preference, Excel provides the option to create custom list for the desired sorting order. This process is called Custom Sorting.

Method #1. How to create a Custom Sorting?

To create a custom sorting, the steps to be followed are,

Step 1: Enter the data in the worksheet based on respective rows and columns.

Step 2: From the data, select the desired cell in the column which needs to be sorted in the order. Here the cell F1 is selected.

Step 3: Choose the Sort Command from Data Tab.

Step 4: A sort dialog box will display. In that enter the value in Sort by list. Here the value is entered as House, where the data are arranged based on the house size such as Small, Medium and Large. In the Sort on choose Values, and choose the Custom List from the order.

Step 4: After selecting the Custom List, the custom list dialog box will appear, in that choose, New List from Custom List. Enter the respective entries in the “List the entries” dialog box. Here the entries are based on the house size, hence add the entries as Small, Medium and Large .Enter the individual entries by clicking the Enter key after entering the single entries. Choose the “Add” button, to add the entries in the Custom List. The new sort order will display in the Custom List box. Select the “New list” in the Custom list and click Ok.

Step 5: The custom lists dialog box will close after pressing the OK button. Press OK in the Sort dialog box where the custom sort is performed.

From the above worksheet, the data is arranged in a custom order like SMALL, MEDIUM and LARGE.

Method #2. How to sort the multiple columns in a data?

From the previous concept, the data is arranged based on custom order using the sort dialogue box. In that method, the values or data present within the column is interchanged. Here in this concept, the columns are sorted based on the values from smallest to largest and largest to smallest using add level option. To sort the single or multiple columns by using add level option, the steps to be followed are,

Step 1: Enter the data in the worksheet based on respective rows and columns.

Step 2: Here the column which contains serial number is selected for sorting.

Step 3: Choose the Sort Command from Data Tab.

Step 4: A sort dialog box will display. Enter the column name in the Sort by list. Here the column name is entered as Serial Number. If one more column needs to be added, choose the add list in the Sort dialog box.

From the above method, the column name serial number is added in the sort by column. If the user wants to add more columns, click the add level option.

The add level option is used to add one or more column. Here the column called “NAME” is chosen where the name is to be sorted from “A to Z”.

From the above image, two columns are selected for sorting based on the order. After the column is selected, the data are arranged in the specified order.

From the above worksheet, the Serial Number is arranged from smallest to largest order. To sort the name from A to Z, same method should be followed.

Summary

From the above method, the steps and methods to customize the data is explained briefly.