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 in 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.
1. Applying SORT Function to Arrange and Create a 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.
Steps:
- Write the below formula in Cell E5 and hit Enter.
=SORT(B5:B13)
- As a result, the formula will give an array of the fruit list which is sorted according to ascending alphabetical order.
⏩ 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.
- Next, from the Excel Ribbon, go to Data > Data Tools group > Data Validation > Data Validation (See the screenshot).
- 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.
- 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.
- After that, click OK.
- As a consequence, the drop-down list is created as expected.
Read More: How to Sort and Filter Data in Excel
2. Combining 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.
Steps:
- Write the below formula in Cell E5.
=UNIQUE(SORT(B5:B13))
- 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 Perform Random Sort in Excel
3. Using OFFSET and COUNTA 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.
Steps:
- Go to Formulas > Define Name > Define Name.
- 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))
- 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)), "")
- 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.
- Finally, upon dragging down the ‘+’ sign, we will receive the list of fruit names arranged alphabetically.
- 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 Do Advanced Sorting in Excel
4. Applying Excel Power Query to Sort Drop-down Data
Now, I will use Excel Power Query to sort a list of data. For ease of my operation, I have converted my dataset to a table by pressing Ctrl + T.
Steps:
- First, select the table (B4:C13).
- Then, go to Data > From Table/Range.
- As a result, the Power Query Editor window will open along with the table.
- Now, right-click on the table, and click on Remove Other Columns as we do not need extra columns.
- Click on the drop-down icon of the fruit column and again click on Sort Ascending.
- 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 below.
- Later, you can create the drop-down list from the above table data.
Read More: How to Perform Custom Sort in Excel
5. Ordering 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.
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.
- Then, right-click on the sheet name and select the View Code.
- As a result, the Microsoft Visual Basic for Applications window will show up. Type the below code in the Module. Remember, you have entered your 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
- 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.
- Consequently, the data in the above table is sorted alphabetically.
- Similarly, the fruits in the drop-down list created earlier are sorted in alphabetical order.
Read More: Advantages of Sorting Data in Excel
Download Practice Workbook
You can download the practice workbook that we have used to prepare this article.
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.
