Custom Sort in Excel (Using Command and Formula)

To custom sort in Excel, we can use the SORT command or a dynamic array formula (SORTBY + MATCH combination) in supported versions.

SORTBY is useful if the sort order column is not part of the data table (a distant column) or if you don’t want to alter the original data.

The purpose of custom sorting in Excel is to arrange the data table based on priorities, which could be the status of tasks, sales orders, etc. In some cases, we will use it to sort by month names, weekdays, quarters, or other sequential text-based orders.

In the following example, we have a two-column dataset with task names in one column and their respective statuses in another column.

Sample Data (A1:B12):

TaskStatus
Drawing ApprovalCompleted
Material ProcurementIn-progress
Supply at siteIn-progress
ExcavationCompleted
Structural workCompleted
Cable LayingIn-progress
End TerminationIn-progress
Cable JointingIn-progress
BackfillingPending
TestingPending
CommissioningPending

I want to sort this data based on the statuses in cells B2:B12 in the order “Completed,” “In-progress,” and “Pending.”

Custom Sort Using SORT Command in Excel

You can follow the steps below to sort the above data using the SORT command in a custom order, rather than alphabetically.

Select the data range A1:B12 as per our example.

Click on the “Data” menu and in the “Sort & Filter” group, click on “SORT.”

Sample data table for custom sorting in Excel with highlighted sort command selection

Check “My data has headers.”

Select the column to sort by, which is “Status” in this case.

Choose “Cell values” in the “Sort On” option, as we want to perform the custom sort based on cell values, not on cell color, font color, or conditional formatting icon.

Select “Custom list” in the “Order” section.

Demonstrating the process of creating a custom list in Excel

This will open the Custom Lists dialog box, which contains pre-filled custom lists such as weekdays, months, and hierarchy or ranking (“Diamond, Platinum, Gold, Silver”).

We need to create a new list for the custom sort order as per our sample data.

To do this, click on “NEW LIST” and enter “Completed”, “In-progress”, and “Pending”. You can enter one value and hit enter, then another, and so on, or enter them comma-separated.

(Alternatively, you can import a list from within a range in your spreadsheet. We will cover that tip once we complete the custom sort).

Once entered the strings, click “Add” and then “OK.”

Built-in custom lists and option to create a new list in Excel

This will close the Custom Lists dialog box. Then click “OK” again to close the Sort dialog box.

This is the simplest way to perform a custom sort in Excel.

Custom Sort Output:

Screenshot displaying the output of a custom sort operation in Excel

Importing List Entries for Custom Lists

In our example, we have only three list entries: Completed, In-progress, and Pending. If you have many, you can import them from your spreadsheet.

To do that, follow the steps below:

  1. Enter the list of entries in your spreadsheet. For example, “Completed,” “In-progress,” and “Pending” in cells D1, D2, and D3 respectively.
  2. Select cells D1:D3.
  3. Click on “File” > “Options.” Then, click “Advanced” and scroll down to “General” options.
  4. Click “Edit Custom Lists,” which will open the Custom Lists dialog box.
  5. Click “Import.”

Note: The list in cells D1:D3 must not be the result of a formula.

Custom Sort Using a Formula in Excel

If you prefer not to alter your original data, you can use a formula to perform custom sorting in Excel. For this, we can utilize the SORTBY function in combination with MATCH.

Please note that this method may not work in all versions of Excel, as it requires dynamic array functionality and the availability of the SORTBY function.

Generic Formula:

=SORTBY(data, MATCH(column, list_entries, FALSE), TRUE)

Where:

  • data – the data table to sort, excluding the header row
  • column – the column to sort by in the data table
  • list_entries – the custom sort order list

Given our sample data in A1:B12 and the list entries present in cells D1:D3 (“Completed,” “In-progress,” and “Pending”), we can use the following formula to sort by custom list in Excel:

=SORTBY(A2:B12, MATCH(B2:B12, D1:D3, FALSE), TRUE)

Resources

  1. Address of the Last Non-Empty Cell Ignoring Blanks in a Column in Excel
  2. Running Total Array Formula in Excel [Formula Options]
  3. Running Count of Occurrences in Excel (Includes Dynamic Array)
Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Cycle Highlights in Google Sheets – Rotate Highlights Daily

Want to cycle highlights in Google Sheets every day? Whether you're rotating a meal...

Filter Rows Containing Multiple Selected Values in Google Sheets

This tutorial explains how to filter rows in a column containing multiple selected drop-down...

Two-Way Lookup with XLOOKUP in Google Sheets

When you need to look up one search key vertically and another horizontally, you...

How to Filter by Total in Google Sheets Pivot Tables

Google Sheets offers many tools to summarize and analyze data, but Pivot Tables are...

More like this

XLOOKUP with Match Mode 3 in Excel (Regex Match)

XLOOKUP becomes more powerful in Excel with the new match mode 3, which enables...

Hierarchical Number Sorting in Excel with Modern Functions

A hierarchical numbering system in Excel allows you to organize data into a clear,...

Dynamic Formula to Sum Every 7 Rows in Excel

To sum every 7 rows, you can use either a drag-down formula or a...

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.