How to create a custom sort when a regular sort won’t do in Excel

Sorting is easy in Excel; you literally click an option and Excel does the rest. Occasionally, if Excel’s not certain how many columns to include in the sort, it will ask, but other than that, sorting is one of the simplest tasks you’ll perform in Excel. I’m talking about ascending and descending sorts, which won’t always be adequate. For instance, what if you want to sort by the days of the week: Monday, Tuesday, Wednesday, and so on? A simple sort won’t get the job done. That’s why Excel includes a custom sort feature. You can create a unique sort order, such as the days of the week. This article will show you how to create a custom sort when you have an irregular sort order.

SEE: 83 Excel tips every user should master (TechRepublic)

I’m using Microsoft 365 desktop on a Windows 10 64-bit system, but you can use earlier versions. Excel Online will run an existing custom sort, but you can’t create them online. For your convenience, you can download the demonstration .xlsx and .xls files. This article assumes you have basic Excel skills, but even a beginner should be able to follow the instructions to success.

How to sort by terms in Excel

A custom sort is one you define. For instance, you might need to sort T-shirts by small, medium, large and extra-large. Or you might sort temperatures by cold, warm and hot. A regular sort can’t handle these types of requirements. In this case, I’ll refer to these sort elements as “terms.” Fortunately, using the Sort feature, you can easily define a custom sort that handles terms.

There are two ways to initiate a custom sort:

• On the Home tab, click Sort in the Editing group and click Custom Sort.
• On the Data tab, click Sort in the Sort & Filter group.

Now, let’s take on a contrived example—I’m doing so because it is so unique, and many of you will have peculiar sorting requirements that make no sense to anyone else but you. Figure A shows a simple data set of regions. You could sort alphabetically, both ascending and descending. But let’s suppose, instead, that you want a specific sort order: Southwest, Central, Northwest. Neither an ascending nor descending sort will work. Southwest, Central, and Northwest are the sort terms.

Figure A

  Let’s create a custom sort by regions.

” data-credit>

To create this sort, do the following:

1. Click anywhere within the data set.
2. Click the Data tab and then click Sort in the Sort & Filter group. (This route requires one less click than the Home tab.)
3. In the resulting dialog, choose Region from the Sort by dropdown because you want to sort by the Region values.
4. Leave Sort On with Cell Values, the default.
5. From the Order dropdown, choose Custom Sort, which will open a new dialog, where you can create the custom sort (list).
6. In the List Entries control, enter Southwest, Central, Northwest—the sort terms in the order by which you want them sorted (Figure A).
7. Click Add to move the new list to the Custom Lists control on the left (Figure B).
8. Click OK to return to the Sort dialog, which will now display the new list in the Order control (Figure C).
9. Click OK to sort the data set.

Figure B

  Add the new list of sort terms. 

” data-credit>

Figure C

  The new list is now the Order setting.

” data-credit>

As you can see in Figure D, the data set now sorts by the custom list. Southwest sorts first, followed by Central, and then Northwest. Custom sorts don’t always make sense, but it’s great to know how this works. 

Figure D

The data set sorts by the custom list.

” data-credit>

To reuse the sort, click Sort and choose Custom Sort from the Order dropdown. Choose the list in the Custom Lists control and click OK.

You might be wondering if a data set can have more than one custom sort. Yes, it can. Let’s create a second custom sort that sorts by the Personnel values in the following order: Rosa, June, James, Martha, Luke, Mark. To do so, repeat the instructions above until step 3 and choose Personnel from the Sort By dropdown. At step 6, enter Rosa, June, James, Martha, Luke, Mark, as shown in Figure E. Click Add, and then click OK twice. Figure F shows the results. You can add as many custom sorts as you need.

Figure E

  Execute a custom sort by personnel.

” data-credit>

Figure F

  This time the custom sort sorts by personnel.

” data-credit>

Admittedly, the examples are a bit contrived and might not make sense—unless you’re the person asked to sort records this way. Then, knowing how to use this feature will make you look great!

Stay tuned

Excel supports a second custom support: sorting by multiple columns. In a future article, I’ll show you how to use this same feature to sort by multiple columns. 

Also see