Today I will be showing how to perform custom sort in Excel. To perform a custom sort, you can use the default custom list, or you can create a custom sort list, and then use it.
While working in Excel, sometimes we have to sort a data set in ascending order. Sometimes we have to sort them in descending order. These are accomplished pretty conveniently using Excel toolbar options or formulas.
But sometimes we have to sort a data set in a custom order. Those who don’t know how to sort a data set in a custom order in Excel, often face trouble while solving these types of problems.
Today I will be showing how you can fix this problem.
How to Perform Custom Sort in Excel (With Easy Steps)
Here we have a data set with the Names, Marks in Mathematics, and Grades of some students of a school called Sunflower Kindergarten.
There are a total of 6 different grades. A+, A, A-, B, C, and F. To highlight them further, I have given them six different colors, from dark to light.
Our objective today is to sort the students according to their grades, from A+ to F.
That means the sorting sequence will be: A+, A, A-, B, C, F.
You can create a sort list in two ways.
Step 1: Create Custom Sort List Using the Custom Lists Dialogue Box in Advanced Options
Here, we will make the custom list directly in the dialog box.
📌 Steps:
- To create a sorted list of this sequence, first, go to File > Options from Excel Toolbar.
- Click on Options. You will get a new window called Excel Options.
- Click on Advanced from the left pane.
- Scroll down through the window. Under the General section, you will find a button called Edit Custom Lists. Click it.
- You will get the Custom Sort dialogue box.
- Under the section Custom lists, you will find all the default sorting lists like Sun, Mon, Tue, Wed, Thu, Fri or Jan, Feb. Mar. Apr. May, Jun. Jul, Aug, Sep, Oct. Nov, Dec.
- Click on NEW LIST.
- Then in the List entries section, write down the names of your custom entries, separated by a comma (,), following your desired sort order. For the sake of this example, write A+, A, A-, B, C, F.
- After that, click on the Add button.
- Then click OK. You will find your list added in the Custom lists section.
Step 2: Import the Custom Sort List from the Workbook
In this section, we will insert a cell reference from the dataset to a custom sort list in Excel
📌 Steps:
- First, create the sorting list in your workbook maintaining the desired sequence.
- Then follow steps 1-3 from the previous section to get the Custom Lists dialogue box.
- Click on the Import icon right to the empty cell named Import list from cells.
- You will find the Custom Lists dialogue box compressed like this. Select the list from the worksheet. The formula will be automatically inserted in the blank box.
- Again click on the Import icon right to the box.
- You will find the Custom List dialogue box back in the expanded form. Click on Import.
- You will find your list added to the Custom lists.
- Click on OK to finish the process.
Read More: How to Do Advanced Sorting in Excel
Step 3: Apply the Custom Sort List
Now we have finished creating the custom sorting list. This time we shall use this list to sort a data set.
📌 Steps:
- Remember our data set with the names, marks, and grades of the students? Select that data set (along with the titles) and go to the Sort & Filter option under the Home tab.
- Click on the drop-down menu. You will see a few options. Select Custom Sort.
- You will get a Dialogue box, Sort.
- Mark My data has headers.
- Under the Column option, select the title of the column according to which you want to sort. For the sake of this example, select Grade.
- Under the Sort On option, select Cell Values.
- And under the Order option, select Custom List.
Excel Shortcut for Custom Sorting:
Alt+A+S+S
- The moment you select Custom List in the Sort dialogue box, you will get the Custom Lists dialogue box. Select your desired list from the Custom lists.
- Click on OK twice (Once in the Custom Lists box and once in the Sort box). You will get your data set sorted according to the custom order.
Our data have been sorted based on the Grade column.
- If you want, you can add another level in the Sort Panel to sort the marks in descending order after sorting according to the grades.
- Click OK. You will get the data set sorted according to the descending order of the marks along with the grades.
Read More: How to Sort and Filter Data in Excel
Custom Sort Not Working in Excel: What Are the Possible Reasons?
In the previous section, we have already shown how to use the custom sort in Excel. But sometimes, it is shown that Custom Sort is not working in Excel. In the below section, we mentioned some reasons and solutions for why the custom sort is not working in Excel.
Reason 1: Custom Sort Is Not Working Due to Blank Cells Present in Your Data
If there are blank cells in the dataset, then custom sorting will not work appropriately. Cells may be blank due to data not being available or any mistake in inserting data in the dataset.
Look at the dataset.
We can see after sorting blank cells are in the last position of the dataset.
Solution:
To solve this, insert the data on the blank cells and apply the custom sort again.
Reason 2: Hidden Rows or Columns Creates Problem in Custom Sorting
When we apply custom sort in a dataset, it will escape if any row or column is hidden.
Solution:
Look at the below image.
Here, Rows 8 and 12 are hidden.
- Now, unhide the hidden rows from the dataset.
Look at the two dataset sets of the image. 1st dataset is sorted data with hidden rows, and 2nd one is sorted without hidden rows. They are not the same. Due to hidden rows, data is not sorted accurately. So, always we will unhide rows and columns from the dataset and then apply custom sorting.
Read More: How to Perform Random Sort in Excel
Reason 3: Custom Sort May Not Work Properly Due to Leading Spaces
Sometimes it is seen that leading spaces exist in the dataset.
Look at the below image.
Here, Rows 17:19 are in the below section of the dataset. We can see elements of the Grade column contain leading spaces and we sorted data based on this Grade column. It considers space as a character and sort data based on this space.
Solution:
To get rid of this we should remove leading spaces from the dataset before sorting data. We can use the TRIM function to remove leading spaces from the dataset.
Reason 4: Presence of Mixed Data Type in the Same Column
If the sorting column contains data of different types, we will not be able to sort them properly.
Look at the below image.
Here, we applied custom sort first based on the Grade column, then based on the Marks column.
The marks column should contain data of number type. But there are some data of text type. Due to different data types sorting based on the Grade column worked successfully, but on the Marks column, the text data occurs in the top position then the number data were sorted.
Solution:
To get rid of this, we must confirm all data of a particular column must be in a single data type.
You can download the following practice workbook to practice while reading this article.
Conclusion
Therefore, using this method, you can sort any data set in any custom order according to your wish. Do you have any questions? Feel free to ask us and visit our website ExcelDemy.
