How to Sort Drop Down List in Excel (5 Easy Methods)

In this tutorial, I will show some methods to sort a drop-down list in excel. Often, when we work with lots of data or repetitive data, if we can sort them alphabetically or any other type of order, navigating the drop-down list becomes easier. So, I will show how to order the list data first and thus use Data Validation to create the drop-down.


Download Practice Workbook

You can download the practice workbook that we have used to prepare this article.


5 Methods to Sort Drop Down in Excel

1. Apply Excel SORT Function to Arrange and Create Drop-Down List

First I will use the SORT function to order data alphabetically. For example, I have a dataset (B4:C13) containing several fruit names in random order. Now, I will order them first.

Apply Excel SORT Function to Arrange and Create Drop-Down List

Steps:

  • Write the below formula in Cell E5 and hit Enter.
=SORT(B5:B13)

Apply Excel SORT Function to Arrange and Create Drop-Down List

  • As a result, the formula will give an array of the fruit list which is sorted according to ascending alphabetical order.

Apply Excel SORT Function to Arrange and Create Drop-Down List

Creating the Drop Down List:

Steps:

  • First, select any of the cells or the whole data range where you want to create the drop-down list.

Apply Excel SORT Function to Arrange and Create Drop-Down List

  • Next, from Excel Ribbon, go to Data > Data Tools group > Data Validation > Data Validation (See the screenshot).

Apply Excel SORT Function to Arrange and Create Drop-Down List

  • Then, the Data Validation dialog box will show up. Choose List from the field: Allow. Choosing the List option will display the field: Source. Click on the upper arrow of the Source field to select the source data.

Apply Excel SORT Function to Arrange and Create Drop-Down List

  • Now input the source data and hit Enter. Here we have used the ‘#’ sign at the end of the source data because we want the whole array of the sorted data to be included in the drop-down list.

Apply Excel SORT Function to Arrange and Create Drop-Down List

  • After that, click OK.

  • As a consequence, the drop-down list is created as expected.

Related Content: How to Arrange Numbers in Ascending Order in Excel Using Formula


2. Combination of SORT & UNIQUE Functions to Sort Drop Down List

Sometimes a list of data contains repetitive values. In that case, you might not want similar data multiple times in a drop-down list. For instance, the below dataset contains Orange, Coconut, and Apple multiple times.  So, now I will use the combination of SORT and UNIQUE functions to sort these data.

Combination of SORT & UNIQUE Functions to Sort Drop Down List

Steps:

  • Write the below formula in Cell E5.
=UNIQUE(SORT(B5:B13))

Combination of SORT & UNIQUE Functions to Sort Drop Down List

  • The above formula will result in an array containing unique fruit names.

  • Similar to Method 1, using the Data Validation option, you can have the drop-down list from the above-sorted fruit names.

Read More: How to Sort Unique List in Excel (10 Useful Methods)


3. Excel Functions with Define Name Option to Organize Drop Down List

This time I will use the Define Name option to get a sorted list. For example, I have a fruit name dataset (A1:A10) in Sheet1 as below. Let’s sort these data alphabetically.

Excel Functions with Define Name Option to Organize Drop Down List

Steps:

  • Go to Formulas > Define Name > Define Name.

Excel Functions with Define Name Option to Organize Drop Down List

  • The New Name dialog box will pop up as a result. Type Fruit in the field: Name and type the below formula in the field: Refers to. Press OK after that.
=OFFSET(Sheet1!$A$1, 0, 0, COUNTA(Sheet1!$A$1:$A$1001))

Excel Functions with Define Name Option to Organize Drop Down List

  • Now go to another sheet (Sheet2). Type the below formula in Cell A1 and hit Enter.
=IF(COUNTA(Fruit)>=ROWS($A$1:A1), INDEX(Fruit, MATCH(SMALL(COUNTIF(Fruit, "<"&Fruit), ROW(A1)),COUNTIF(Fruit, "<"&Fruit), 0)), "")

Excel Functions with Define Name Option to Organize Drop Down List

  • The above formula will return one fruit name which comes first in alphabetical order. Drag down the ‘+’ sign to get the rest of the fruit names.

Excel Functions with Define Name Option to Organize Drop Down List

Excel Functions with Define Name Option to Organize Drop Down List

  • Next, you can create the drop-down list using the Data Validation option, following Method 1. Remember this time, you have to select the above list of the sorted fruit names as source data.

Read More: How to Sort by Name in Excel (3 Examples)


Similar Readings:


4. Use Excel Power Query to Sort Drop Down Data

Now, I will use Excel Power Query to sort a list of data. For the ease of my operation, I have converted my dataset to a table pressing Ctrl + T.

Use Excel Power Query to Sort Drop Down Data

Steps:

  • First, select the table (B4:C13).

Use Excel Power Query to Sort Drop Down Data

  • Then, go to Data > From Table/Range.

Use Excel Power Query to Sort Drop Down Data

  • As a result, the Power Query Editor window will open along with the table.

Use Excel Power Query to Sort Drop Down Data

  • Now, right-click on the table, and click on Remove Other Columns as we do not need extra columns.

Use Excel Power Query to Sort Drop Down Data

  • Click on the drop-down icon of the fruit column and again click on Sort Ascending.

Use Excel Power Query to Sort Drop Down Data

  • The fruit list will be sorted in alphabetical order consequently.

  • After you are done with the sorting, select Close & Load > Close & Load from the Power Query Editor.

  • As a consequence, the table containing sorted fruit names is displayed as below.

  • Later, you can create the drop-down list from the adobe table data.

Related Content: Excel Auto Sort When Data Changes (9 Examples)


5. Order Drop Down List Using VBA in Excel

Here, I will use VBA to sort fruit names in alphabetical order. For example, we have a drop-down list where fruit names are not sorted in any order.

Order Drop Down List Using VBA in Excel

So, I will show how to order the above drop-down list alphabetically.

Steps:

  • First, I will go to the sheet where the source data of the above drop-down list is. Here, my source data are located in Sheet8.

Order Drop Down List Using VBA in Excel

  • Then, right-click on the sheet name and select the View Code.

Order Drop Down List Using VBA in Excel

  • As a result, Microsoft Visual Basic for Applications window will show up. Type the below code in the Module. Remember, you have entered the Sheet Name, Table Name, and Column Name of yours correctly.
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim rngSort As Range
'Set reference to the Table and column to be sorted
Set rngSort = ThisWorkbook.Worksheets("Sheet8").Range("FruitName[Fruit]")
'Use Range.Sort method to apply sort
rngSort.Sort _
Key1:=rngSort, _
Order1:=xlAscending, _
Header:=xlYes

End Sub

Order Drop Down List Using VBA in Excel

  • Now, go to the source data table and write any fruit like ‘Dates’ in Cell B14, after the last data of the table (B4:B13). Press Enter after that.

Order Drop Down List Using VBA in Excel

  • Consequently, the data in the above table is sorted alphabetically.

  • Similarly, the fruits in the drop-down list created earlier are too sorted in alphabetical order.

Read More: How to Sort ListBox with VBA in Excel (A Complete Guide)


Conclusion

In the above article, I have tried to discuss several methods to sort a drop-down list in excel elaborately. Hopefully, these methods and explanations will be enough to solve your problems. Please let me know if you have any queries.


Related Articles

Hosne Ara

Hosne Ara

Hi, This is Hosne Ara. Currently, I do write for ExcelDemy. I have a long experience working with different industries and I have seen how vast the scope of Microsoft Excel is. So, eventually, I started to write articles on Excel and VBA. Basically, my articles are targeted to help people who are working in Excel. By profession, I am an Engineer. Materials and Metallurgical Engineering is my major. Besides, I am a certified Project Manager (PMP) too. I have worked with Power Plant and IT industry earlier. As a person, I am detail-oriented and love doing research. Establishing a greener world is one of my mottos.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo