Custom Sort in Excel

This tutorial explains how to create a custom sort order in Excel with two common examples.

Numeric values naturally have an inherent order that can be specified as ascending or descending. Text values are typically sorted in A-Z or Z-A order. However, there are instances when we need to establish a specific order for our text values.

Let’s look at how to create a custom sort in Excel for text values.

Download the practice file to follow along.

Custom Sort for Text Values

To specify a custom sort order for text values in Excel, we will use the Custom Lists feature.

There are two methods for creating a custom list – to enter the values, or to import them from a list on a worksheet.

Enter values in the specified order

Consider the [Priority] column, which contains text values. We would like to sort the column in the order of Max, High, Normal, and Low. This cannot be achieved with the natural A-Z sort order for text values. We need to create a custom sort order to specify the desired order.

To specify the sort order, follow these steps:

1. Go to the File tab and select Options.
2. In the Excel Options window, choose the Advanced tab.
3. Scroll down and click Edit custom lists. This will open the Custom Lists window.
4. In the List entries box, enter the desired order for your text values.
5. Click the Add button to include them in the Custom lists field.
6. Click OK to save the custom list.

The new list will now be added to the Excel custom lists and can be used for a Fill sequence, similar to how the days of the week or months of the year can be entered onto a sheet. More importantly, the list can be used to state a custom sort order.

Sorting in Excel by Custom List

Now that we have created our custom list, let’s proceed to sort the [Priority] column in that order. 

To sort the [Priority] column using the custom list, follow these steps:

1. Right-click on a value within the [Priority] column.
2. Navigate to the Sort option, and choose Custom Sort from the sub-menu. The Sort window will open.
3. Select the [Priority] column in the Sort by column.
4. Specify Cell Values for the Sort On option.
5. Click on the dropdown arrow in the Order field and choose Custom List.
6. Specify the custom list to use.
7. Click OK to apply the custom sort.

This will sort the [Priority] column in the specified custom order.

Import Custom List from Cell Values

Another common scenario of needing custom sort is when sorting the names of months. By default, the month order is January through December. However, there may be instances when we need to sort the [Month] column based on the specified [Month Order] column.

To import a custom list from cell values, follow these steps:

1. Enter the text order within a range in your spreadsheet
2. Go to the File tab in Excel.
3. Select Options.
4. In the Excel Options window, choose the Advanced tab.
5. Scroll down and select Edit custom lists. The Custom Lists window will open.
6. Click inside the Import list from cells field.
7. Select the values from the [Month Order] column.
8. Click Import>OK.

Custom Sort Months

Now that we’ve created a custom order for our months, we will proceed to sort the [Month] column according to the specified order of the imported custom list, by following the exact steps mentioned in the previous Sorting in Excel by Custom List section of this tutorial.

The [Month] column we be sorted in this order:

Custom sorting in Excel allows you to define specific orders for your text values beyond the default A-Z or Z-A sorting options. By creating custom lists or importing values from cells, you can sort your data in a tailored manner, enhancing your data analysis capabilities in Excel.

I hope this information proves helpful. Let me know in the comments!