How to create a dynamic drop down list in alphabetical order in Excel?
Most of you may know how to create a dynamic drop down list, but in some cases, you may want to sort the drop down list in alphabetical order as below screenshot shown. Now this tutorial introduces the way to create a dynamic drop down list in alphabetical order in Excel。
AutoText (save charts, formulas, pictures as auto text, then you can insert them in anysheet when you need.)
Recommended Productivity Tools for Excel
Office Tab: Bring powerful tabs to Office (include Excel), just like Chrome, Safari, Firefox and Internet Explorer. Save you half the time, and reduce thousands of mouse clicks for you. 30-day Unlimited Free Trial
Kutools for Excel: Save 71% of your time and solve 82% Excel problems for you. 300+ advanced tools designed for 1500+ work scenario, make Excel much easy and increase productivity immediately.60-day Unlimited Free Trial
To create a dynamic drop down list in alphabetical order, you need to do as below steps.
|Kutools for Excel, with more than 120 handy Excel functions, enhance your working efficiency and save your working time.|
1. Select the original data then click Formulas > Define Name. see screenshot:
2. Then in the New Name dialog, type a name for the selected range in the Name text box, and type this formula =OFFSET(Sheet1!$A$1, 0, 0, COUNTA(Sheet1!$A$1:$A$1001)) into Refers to text box. See screenshot:
Note: In the formula, A1 is the first cell of your original data range, and sheet1 is the worksheet you original data is in.
3. Click OK. Then go to another sheet, here I go to Sheet2, and select a cell to type this formula =IF(COUNTA(List)>=ROWS($A$1:A1), INDEX(List, MATCH(SMALL(COUNTIF(List, "<"&List), ROW(A1)), COUNTIF(List, "<"&List), 0)), "") into it, and press Shift + Ctrl + Enter keys, then drag the autofill handle down until blank cell appears. See screenshot:
Note: In the above formula, List is the name you define for the original data in step 2, A1 is the first cell of the original data.
4. Then select the column the formula cells in, and click Formula > Define Name, then in the Name text box type SortedValues in the New Name dialog. See screenshot:
Export Range to File
|Kutools for Excel's Export Range to File function can export or save a range to separate file as
workbook, pdf, text, csv or text.
|Click Enterprise > Import/Export > Export Range to File.|
5. Click OK. Then go to select a cell or a range you need to create the dynamic drop down list, and click Data > Data Validation. See screenshot:
6. Then in the Data Validation dialog, select List from the Allow list, and type =SortedValues into Source text box. See screenshot:
7. Click OK. Now you can see the created drop down list is sorted in alphabetical order.
Tip: If you add new data to the original data, the data will be automatically added to the drop down list and sort them again. See screenshot:
|If you are interested in this addi-in, download the 60-days free trial.|
Recommended Productivity Tools
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
To post as a guest, your comment is unpublished.· 2 years agoExcellent tutorial, ty so much! I noticed that my dropdown is displayed scrolled down, where there are blanks. Yours doesn't seem to do so. How can I make the list display the "a" at the top instead of blanks after the "z" and having to scroll up to "a". Thank you so much.
To post as a guest, your comment is unpublished.· 3 years agoExcellent example, but if i add 10 news ? do i need to copy the formula on the item 3 ?