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

Skill Level: Intermediate

Video

Watch the video at the top of the page.

4 Ways to Automatically Sort Drop Down Lists Excel 640

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.

Automatically Sort Data Validation.xlsm (92.7 KB)

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.

Source list sorted automatically with macro..

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

Drop down list sorted automatically with macro

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:

List Search Button

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.

List Search Sort Ascending Button.

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.

From Table Range on Data Tab

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

Power Query Remove Other Columns

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

Power Query 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.

Close & Load

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! 🙂

8 comments

Your email address will not be published. Required fields are marked *

  • I see at 3:45 in the video that Data Validation is set and allows for automatic sorting of the entries in the rngProducts source so what’s the point in using any other method of sorting(macros, add-ins, power query, dynamic array functions) since this does it?

  • Dear Sir Jon, I able to put XL Campus on my Excel Ribon. I tried to add List Search and Help Page under the XL Campus Ribon but I failed. So, do you have any Vedeo, Free lesson or Instructions about these two subjects? or how can I do these two things in my computer under XL Campus Ribon. Please Help me with out any hesitate.

    Best Wishes

    Rana Jang Bahadur

  • I like the spill ref notation# to reference the entire spill range but when trying on my own, Excel shows an error message. I have Excel 365 subscription. Is this feature also available to Insiders Fast Build only?

  • PS – I am aware that I can press ALT to display keyboard shortcuts for the items in the quick access toolbar – but I do have over 20 items there and prefer to use my own shortcut keys

    thanks

    Harvey

  • Hi

    I really like the dropdown lookup function – but try to avoid using my mouse.

    Can you please tell me how to add a shortcut key – perhaps CTRL-l (L = lookup) – so all I need do is ctrl-l then start typing the data a want to locate

    (I did add it to the quick access toolbar – but that is still not ideal)

    Many thanks

    Harvey

JOIN US & LEARN EXCEL

Learn 10 great Excel techniques that will wow your boss and make your co-workers say, "how did you do that??"
Plus weekly updates to help you learn Excel.

Download the eBook

About Me

Jon Acampora Profile

Hello and welcome! My name is Jon Acampora and I'm here to help you learn Excel.

This blog is updated frequently with Excel and VBA tutorials & tools to help improve your Excel skills and save time with your everyday tasks. Subscribe above to stay updated. More about me...

MVP_Horizontal_BlueOnly