How to Sort Drop Down Lists Automatically in Excel

Bottom Line: Learn 4 ways to automatically sort a data validation or drop-down list.

Skill Level: Intermediate

Video Tutorial

Download the Excel File

Here's a copy of the file I use in the video so you can practice sorting data validation lists automatically.

4 Ways to Sort Drop Down Lists

We received a great question in the comments of my previous YouTube video on How to Add New Items to a Drop-down List Automatically. Suliman, Wim, and Colin wanted to know if it is possible to automatically sort the drop-downs whenever new entries are added to the source range.

The answer to the question is: absolutely. And while I mention a macro as the solution in my response to the question, there are actually a variety of ways to accomplish this.

In this post I explain four different solutions, and you should be able to use at least one of these regardless of which version of Excel you are using.

1. Sorting Drop Down Lists with a VBA Macro

Applies to: All desktop versions of Excel for Windows and Mac.

Below is VBA code for a macro that runs automatically any time a change is made to the worksheet that contains the source data for your drop-down list. The macro sorts the data range in ascending order, and that carries over to the drop down list that is being fed by the source range.

You can substitute the name of your workbook, worksheet, and column to reflect whatever range you are working with.

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("Products").Range("tblProducts[Products]")

  'Use Range.Sort method to apply sort
  rngSort.Sort _
    Key1:=rngSort, _
    Order1:=xlAscending, _
    Header:=xlYes

End Sub

This techniques uses the Worksheet_Change event to automatically run the macro anytime a change is made to the worksheet. If you want to learn more about events that can trigger a macro to run and how to set that up, check out this tutorial on VBA Code Modules & How to Run Macros Based on User Events.

Here's a side by side comparison of the source list before and after running the macro.

And this comparison shows the corresponding drop down list before and after the macro runs.

These automatic updates to your drop down list also apply when you add entries to or delete entries from the source range. It's best to use an Excel Table and named range to reference the entire Table column. I explain that setup in my previous post on How to Add New Rows to Drop-down Lists Automatically.

This first method for sorting data validation lists is pretty universal in terms of access. Anyone using a desktop version of Excel on either Windows or Mac should be able to use a macro to automatically sort their drop downs.

2. Sorting Drop Down Lists with the List Search Add-in

Applies to: All desktop versions of Excel for Windows

The next option for sorting drop down lists uses a free Excel add-in that I created. If you don't yet have this List Search Add-in installed, you can access it from this post:

How to Search Data Validation Drop-down Lists in Excel.

Once you have the List Search Add-in installed, you will have a new tab in your Ribbon labeled XL Campus. You'll see the List Search section that looks like this:

With any cell selected that has a data validation list, just click on that List Search button, and a List Search window will appear below the cell.

By clicking on the Options button (highlighted in orange above), the window will be expanded to show you more buttons, including those that will sort your list in ascending or descending order.

Keep in mind that sorting the list in the list search does not make changes to the source data list or the drop down list on the Excel sheet itself. So this can be a good option if you don't want to make changes to your source sheet.

You can open the List Search window on any Excel file to search existing drop-down lists, and view the sorted list. This means there is no additional setup work required within the Excel file. All of the other solutions mentioned will require setup work with either a macro, Power Query, or formulas.

In terms of accessibility, the Free List Search Add-in is available for Excel for Windows 2007 or later, including Office 365. The add-in has some other great features, and was created to help you save time entering data into cells when you have long data validation lists.

3. Sorting Drop Down Lists Using Power Query

Applies to: Excel for Windows versions 2010 or later including Office 365.

If you're not familiar with Power Query, this option might be a little harder to follow since it has more steps. I recommend you get acquainted with Power Query by checking out this overview I put together:

Power Query Overview: An Introduction to Excel’s Most Powerful Data Tool.

1. To begin, add the table that holds your source content into Power Query by going to the Data tab on the Ribbon and choosing From Table or Range. You can also use an external source like a database or website for your query.

2. Right-click the column header for the range you want and select Remove Other Columns.

3. Right-click the column header again and choose Remove Duplicates.

4. Sort the column alphabetically using the drop down at the top. This produces a list of unique entries, sorted in alphabetical order.

5. Click Close & Load. This inserts a new sheet with the output table.

6. A Table is output on a new sheet in the workbook.

This new Table can become the source range for your data validation list. You will still want to use the named range technique to reference the Table column for data validation, and I explain more in this post on drop-down lists that automatically update.

This option is best for when data is coming from an outside database or source, and changes are not necessarily being made manually.

Any time new data is added to the source sheet, you can refresh the query and it will automatically output those updates to the existing Table. The drop-downs that use the Table as a source range will automatically display the changes in the sorted list.

4. Sorting Drop Down Lists Using Dynamic Array Functions

Applies to: Excel for Desktop Office 365 (Insiders Fast Build)

This is my favorite of the options because it utilizes a revolutionary new feature for Excel, Dynamic Array Functions and Spill Ranges. Unfortunately this feature is only accessible on the Insider's Fast Build on Office 365. It is a free program to all Office 365 subscribers, and I explain more about it in this post on Dynamic Array Formulas.

Even if you don't have dynamic arrays accessible to you today, checkout how easy they make this process.

We will use the new SORT function to create a formula that references the list we are using for the drop-down. This formula will return a sorted list in the spill range (the blank cells below the formula).

The new spill range will now become the source of the data validation list.

We can use the spill ref notation to reference the entire spill range. This is done by referencing the first cell of the spill range, then adding a # symbol after it. See the screenshot below.

As items are added/deleted to the source range in column A, the spill range in column C will automatically update. Since the spill range is referenced in the source of the data validation, the drop-down list will also automatically update.

This means you only have to set this up once and the drop-down list will always be sorted.

This technique can also work in place of the technique #3 we saw with Power Query and removing duplicates.

We can use the UNIQUE and SORT functions to return a sorted list of unique values to a spill range. Then reference that range as the source of the data validation.

Conclusion

So there you have it. Four different ways to sort data for drop down lists. While the dynamic array option is by far the coolest and simplest, it's not currently accessible to everyone. Hopefully that will change soon.

One other related post that might interest you shows you how to make the drop-down arrow permanently visible even after you've selected another cell:

Drop-down List Arrow Always Visible for Data Validation.

And if you're not familiar with drop-down lists yet, checkout my post and video on How to Create Drop-down Lists in Excel – Data Validation Lists. It's a complete guide that covers handling invalid inputs and applying drop-downs to other cells.

There are more ways than these four to automatically sort a drop down list. If you have one you'd like to share then please leave a comment below. We'd love to hear from you.

Thanks and have a great day! 🙂