# How to Perform Custom Sort in Excel – 3 Steps

The sample dataset showcases Names, Marks in Mathematics, and students’ Grades.

There are 6 different grades. A+, A, A-, B, C, and F. They are highlighted in six different colors (dark to light).

### Step 1 – Create a Custom Sort List Using the Custom Lists Dialog Box in Advanced Options

Steps:

• Go to File > Options.

• In Excel Options, click Advanced.
• In General, click Edit Custom Lists.

• In the Custom Sort dialog box, select Custom lists.
• Click NEW LIST.
• In List entries, enter A+, A, A-, B, C, F.

• Click OK.

The list is added to the Custom lists.

### Step 2 – Import the Custom Sort List from the Workbook

Steps:

• Create the sorting list in your workbook maintaining the sequence.

• Follow steps 1-3 in the previous section to open the Custom Lists dialog box.
• Click Import.

• Select the list in the worksheet. The formula will automatically be inserted in Import list from cells.
• Click Import.

The list is added to the Custom lists.

• Click OK.

### Step 3 – Use the Custom Sort List

Steps:

• Select the dataset (including the headers) and go to Sort & Filter in the Home tab.
• Select Custom Sort.

• In Sort, check My data has headers.
• In Column, select a title. Here, select Grade.
• In Sort On, select Cell Values.
• In Order, select Custom List.

Excel Shortcut for Custom Sorting:

Alt+A+S+S

• In the Custom Lists dialog box, select the list in Custom lists.

• Click OK in Custom Lists and in Sort.

The dataset is sorted based on the Grade column:

• In Then by, enter Marks.
• In Sort On, select Cell Values.
• In Order, choose Largest to Smallest.

• Click OK.

Read More: How to Sort and Filter Data in Excel

## The Custom Sort is Not Working in Excel – Possible Reasons:

Reason 1: Custom Sort Is Not Working Due to Blank Cells Present in Your Data

There are blank cells in the dataset:

After sorting, blank cells are at the bottom of the dataset.

Solution:

• Enter data in the blank cells and custom sort again.

Reason 2: Hidden Rows or Columns Create Problems in Custom Sorting

Solution:

• Unhide the hidden rows.

The two datasets show sorted data with hidden rows, and without hidden rows.

Read More: How to Perform Random Sort in Excel

Reason 3: Custom Sort May Not Work Due to Leading Spaces

Solution:

Remove leading spaces using the TRIM function.

Reason 4: Mixed Data Types in the Same Column

Solution:

Make sure data in the column is in the same type.

## Related Articles

<< Go Back to Sort in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Rifat Hassan

Rifat Hassan, BSc, Electrical and Electronic Engineering, Bangladesh University of Engineering and Technology, has worked with the ExcelDemy project for almost 2 years. Within these 2 years, he has written over 250 articles. He has also conducted a few Boot Camp sessions on effective coding, especially Visual Basic for Applications (VBA). Currently, he is working as a Software Developer to develop and deploy additional add-ins to enhance the customers with a more sophisticated experience with Microsoft Office Suits,... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF