Skip to main content

Kutools for Office — One Suite. Five Tools. Get More Done.

How to create a dynamic drop down list in alphabetical order in Excel?

Author Sun Last modified

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。

A screenshot showing a dynamic drop-down list sorted in alphabetical order in Excel

Create dynamic drop down list in alphabetical order

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.

1. Select the original data then click Formulas > Define Name. see screenshot:

A screenshot showing the selection of data and Define Name option in Excel

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:

A screenshot showing the New Name dialog

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:

A screenshot showing the formula being applied to sort values for the drop-down list in Excel

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:
A screenshot showing the Define Name option for sorted values in Excel
A screenshot showing the New Name dialog for defining sorted values

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:

A screenshot showing the Data Validation option for creating a dynamic drop-down list in Excel

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

A screenshot showing the Data Validation dialog

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

A screenshot showing the final dynamic drop-down list in alphabetical order in Excel

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:

A screenshot showing the dynamically updated drop-down list after adding new data in Excel

Related Articles:

Best Office Productivity Tools

🤖 Kutools AI Aide: Revolutionize data analysis based on: Intelligent Execution   |  Generate Code  |  Create Custom Formulas  |  Analyze Data and Generate Charts  |  Invoke Kutools Functions…
Popular Features: Find, Highlight or Identify Duplicates   |  Delete Blank Rows   |  Combine Columns or Cells without Losing Data   |   Round without Formula ...
Super Lookup: Multiple Criteria VLookup  |   Multiple Value VLookup  |   VLookup Across Multiple Sheets   |   Fuzzy Lookup ....
Advanced Drop-down List: Quickly Create Drop Down List   |  Dependent Drop Down List   |  Multi-select Drop Down List ....
Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Toggle Visibility Status of Hidden Columns  |  Compare Ranges & Columns ...
Featured Features: Grid Focus   |  Design View   |   Big Formula Bar   |  Workbook & Sheet Manager   |  Resource Library (Auto Text)   |  Date Picker   |  Combine Worksheets   |  Encrypt/Decrypt Cells   |  Send Emails by List   |  Super Filter   |   Special Filter (filter bold/italic/strikethrough...) ...
Top 15 Toolsets:  12 Text Tools (Add Text, Remove Characters, ...)   |   50+ Chart Types (Gantt Chart, ...)   |   40+ Practical Formulas (Calculate age based on birthday, ...)   |   19 Insertion Tools (Insert QR Code, Insert Picture from Path, ...)   |   12 Conversion Tools (Numbers to Words, Currency Conversion, ...)   |   7 Merge & Split Tools (Advanced Combine Rows, Split Cells, ...)   |   ... and more
Use Kutools in your preferred language – supports English, Spanish, German, French, Chinese, and 40+ others!

Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time.  Click Here to Get The Feature You Need The Most...

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!

All Kutools add-ins. One installer

Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.

Excel Word Outlook Tabs PowerPoint
  • All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
  • One installer, one license — set up in minutes (MSI-ready)
  • Works better together — streamlined productivity across Office apps
  • 30-day full-featured trial — no registration, no credit card
  • Best value — save vs buying individual add-in