In Excel, in Microsoft 365, how do you sort your dropdown data validation list? You have a cell where you want the user to pick from a given list. For the user’s benefit and convenience, you want that list to be sorted alphabetically. This way they can find it faster. In Excel, the dropdown list is by default in the order that the list appears on the sheet. We want to change that. This solution only works if you have Microsoft 365.

Below is a screenshot of an Excel sheet. There is a lot in here! The video below will help to explain how this works. If you are new to some of this, you can go to dynamic arrays and start there. This accomplished with the formula that you can see displayed in cell H8 in the screenshot. That formula is located in cell J2. It spills down as you can see. Cell F2 has data validation. The list that the user picks from can be seen in the spilled range in column J. It is sorted, unique and has no blanks.

Learning with YouTube

Here’s a video called Excel Dynamic Arrays: Data Validation List Dropdown: Unique, Sorted & No Blanks EMT 1523 by ExcelIsFun. This only works with Microsoft 365’s version of Excel. ExcelIsFun goes beyond in this video. He shows how to create your dropdown list to be unique, sorted and with no blanks.