Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in
x
or
x
x
Register
x

or

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。

doc-drop-down-list-alphabetically-1

Create dynamic drop down list in alphabetical order


AutoText (save charts, formulas, pictures as auto text, then you can insert them in anysheet when you need.)

doc autotext face

Office Tab Enable Tabbed Editing and Browsing in Office, and Make Your Work Much Easier...
Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%
  • Reuse Anything: Add the most used or complex formulas, charts and anything else to your favorites, and quickly reuse them in the future.
  • More than 20 text features: Extract Number from Text String; Extract or Remove Part of Texts; Convert Numbers and Currencies to English Words.
  • Merge Tools: Multiple Workbooks and Sheets into One; Merge Multiple Cells/Rows/Columns Without Losing Data; Merge Duplicate Rows and Sum.
  • Split Tools: Split Data into Multiple Sheets Based on Value; One Workbook to Multiple Excel, PDF or CSV Files; One Column to Multiple Columns.
  • Paste Skipping Hidden/Filtered Rows; Count And Sum by Background Color; Send Personalized Emails to Multiple Recipients in Bulk.
  • Super Filter: Create advanced filter schemes and apply to any sheets; Sort by week, day, frequency and more; Filter by bold, formulas, comment...
  • More than 300 powerful features; Works with Office 2007-2019 and 365; Supports all languages; Easy deploying in your enterprise or organization.

arrow blue right bubble Create dynamic drop down list in alphabetical order


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:

doc-drop-down-list-alphabetically-2

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:

doc-drop-down-list-alphabetically-3

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:

doc-drop-down-list-alphabetically-4

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:

doc-drop-down-list-alphabetically-5
doc-drop-down-list-alphabetically-6

goodExport 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.
doc export cell 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:

doc-drop-down-list-alphabetically-7

6. Then in the Data Validation dialog, select List from the Allow list, and type =SortedValues into Source text box. See screenshot:

doc-drop-down-list-alphabetically-8

7. Click OK. Now you can see the created drop down list is sorted in alphabetical order.

doc-drop-down-list-alphabetically-9

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:

doc-drop-down-list-alphabetically-10

pay attention1If you are interested in this addi-in, download the 60-days free trial.

Relative Articles:


Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

  • Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
  • Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
  • Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
  • Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
  • Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
  • Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
  • Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
  • Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
  • More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial.
kte tab 201905

Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
officetab bottom
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    carsten · 5 months ago
    The first navn i shown in the first 155 cells. Does any know why
  • To post as a guest, your comment is unpublished.
    Natalia · 3 years ago
    Excellent 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.
    Alexandre · 3 years ago
    Excellent example, but if i add 10 news ? do i need to copy the formula on the item 3 ?