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 Add.

• 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.

Read More: How to Do Advanced Sorting in Excel

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.

• 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:

• Click Add Level.
• 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.

Download Practice Workbook

Download the following practice workbook.

Related Articles

• How to Sort Drop Down in Excel
• How to Sort IP Address in Excel
• How to Sort Excel Tabs
• Advantages of Sorting Data in Excel
• Difference Between Sort and Filter in Excel

<< Go Back to Sort in Excel | Learn Excel