How to sort table using Custom list in Excel

In this article, we will learn How to sort tables using the Custom list in Excel.

Problem:

Using a table with date values, the user faces problems when the data needs sorting on the basis of priority or given pattern. For example sorting data based on days of week Sun, Mon, Tues, Wed, Thurs, Fri, Sat or sort table on month of year Jan, feb, Mar, apr, May, Jun, Jul, Aug, Sept, Oct, Nov, Dec. You must be wondering by what happens when we sort days of week by A-Z or Z-A (standard sorting). You will get it as shown below.

This is not at all the order of weekdays. Let's learn how to use a saved custom list in Excel.

Custom list in Excel

Custom lists in excel are used to sort tables on the basis of given priority level. Like sorting delivery status of orders first delivered, pending and cancelled. Excel also provides with its saved some list like days of week and months of year.

Choose saved custom lists from Advanced sorting

Go to Data > Sort > choose date column > Choose custom list Order by > select the recommended list (days of week or month of year)

Create your own custom list

List your priority order in a different column. Go to File > Options > Advanced > Edit custom list > Create new list > import priority order cells > Add list and click Ok. Now you can access this custom list (priority order) under advanced sorting.

Example :

All of these might be confusing to understand. Let's understand how to use the function using an example. Here we have a sample data which we need to sort by month of year.

Go to Data > Sort. This will open the advanced sorting dialog box.

Advanced sorting dialog box appears.

Choose column name and choose Custom list as shown in the above snapshot. Click OK. This will open the custom list dialog box.

Choose the required option from saved lists in the Custom lists and Click Ok. The table is sorted by date column on the months of the year.

As you can see clearly the date column is sorted along with the table. Now we need to sort the data based on a made up custom list.

Create your own custom list

Here we have data and we need to sort the table where all the delivered orders come first and then pending and then cancelled orders. This is a custom list which we need to add in the saved custom lists.

To add the custom list add your priority list in a column.

Fo to File > Options. On the left pane.

Clicking options you will open Excel Options dialog box. Go to Advanced option in the left pane. Find the General label and select the Edit Custom list as shown below.

Custom list dialog box appears. Click Edit Custom Lists.

Import the three values in the priority order. You can either type or import using cell reference. Click ADD and Click Ok. Now you will see the new list in the custom lists.

Go to Data > Sort > choose date column > Choose custom list Order by > Custom lists > Choose your list and click ok.

Click Ok to confirm. Now you can see your table sorted in the required way.

You don't need to add lists again and again. Just add once and use whenever you want.

Here are all the observational notes using the formula in Excel
Notes :

1. Select any cell of the table, before proceeding to the explained method.
2. Use standard Sort & filter, if you need to sort table ascending or descending column

Hope this article about How to sort tables using the Custom list in Excel is explanatory. Find more articles on sorting or filtering values and related Excel tools here. If you liked our blogs, share it with your friends on Facebook. And also you can follow us on Twitter and Facebook. We would love to hear from you, do let us know how we can improve, complement or innovate our work and make it better for you. Write to us at info@exceltip.com.

Related Articles :

How to use Shortcut Keys for Sort & filter in Excel : Use Ctrl + Shift + L from keyboard to apply sort and filter on table in Excel.

The SORT Function in Excel 365 (new version) : The SORT function returns the sorted array by the given column number in the array. It also works on horizontal data.

Sort numbers using Excel SMALL Function : To sort numbers using formula in Excel 2016 and older, you can use the SMALL function with other helping functions.

Sort Numeric Values with Excel RANK Function : To sort the numeric values we can use the Rank function. The formula is

Excel Formula to Sort Text : To sort text values using formula in excel we simply use the COUNTIF function. Here is the formula

Excel Formula to Extract Unique Values From a List : To find all unique values from a list in Excel 2016 and older versions, we use this simple formula.

UNIQUE Function for Excel 2016 : The Excel 2016 does not provide the UNIQUE function but we can create one. This UDF UNIQUE function returns all unique values from the list.

Popular Articles :

How to use the IF Function in Excel : The IF statement in Excel checks the condition and returns a specific value if the condition is TRUE or returns another specific value if FALSE.

How to use the VLOOKUP Function in Excel : This is one of the most used and popular functions of excel that is used to lookup value from different ranges and sheets.

How to use the SUMIF Function in Excel : This is another dashboard essential function. This helps you sum up values on specific conditions.

How to use the COUNTIF Function in Excel : Count values with conditions using this amazing function. You don't need to filter your data to count specific values. Countif function is essential to prepare your dashboard.