How to Create Dynamical Drop-Down List and Sort by Alphabetical Order in Excel

In our daily work we may need to create a dynamical dropdown list and sort all values by alphabetical order. To create a dropdown list like this, we need to apply some built-in features like ‘Define Name’ and ‘Data Validation’ features, and we also need the help of formula which is combined with excel functions. It sounds complex, but through this tutorial we will introduce you the way to create dynamical dropdown list clearly, we will show you the details by steps, you can follow each step to complete all operations and reach your goal finally.

Precondition:

Prepare a list full of different fruits. We may also add new fruits into the list. Now we want to create a dynamical dropdown list and sort fruits by alphabetically order.

How to Create Dynamical Drop-Down List 1

Method: Create Dynamical Drop-Down List and Sort Alphabetically


Step 1: Select this fruit list and click Formulas in ribbon, and select Define Name under Defined Names group.

How to Create Dynamical Drop-Down List 2

Step 2: In New Name dialog, enter Name as ‘List’, keep Scope unchanged, enter formula =OFFSET(Sheet1!$A$1, 0, 0, COUNTA(Sheet1!$A$1:$A$1001)) into Refers to. Then click OK. Please notice that in this formula ‘Sheet1’ is current sheet name, $A$1 is the first cell of your selected data.

How to Create Dynamical Drop-Down List 3

Step 3: Create sheet2, in A1 enter the formula =IF(COUNTA(List)>=ROWS($A$1:A1), INDEX(List, MATCH(SMALL(COUNTIF(List, “<“&List), ROW(A1)), COUNTIF(List, “<“&List), 0)), “”). In this formula, ‘List’ is defined name in last step.

How to Create Dynamical Drop-Down List 4

Step 4: As above formula is an array formula, so press Ctrl+Shift+Enter to get value.

How to Create Dynamical Drop-Down List 5

We get the first fruit ‘Apple’ from original table.

Step 5: Drag the fill handle down till blank cell displays.

How to Create Dynamical Drop-Down List 6

Note:

If you are confused about above formula and cannot apply it correctly in your work with your instance, you can directly copy the whole list from sheet1 to sheet2, and then sort them by A->Z. Then when you adding new parameters into sheet1 original list, you have to copy this list into sheet2 and sort again.

How to Create Dynamical Drop-Down List 7

Step 6: Click Formulas in ribbon, and select Define Name under Defined Names group. Enter ‘SortList’ as Name, enter =Sheet2!$A$1:$A$20 into Refers to. Then click OK. Please notice that as we create a dynamically dropdown list, so we can add new parameters into the list without limit, in this instance we set ‘Refer to’ range $A$1:$A$20, so only the first 20 cells in A column can be listed in dropdown list. You can change the reference per your demand.

How to Create Dynamical Drop-Down List 8

Step 7: Select C2 to locate dropdown list. Click Data->Data Validation under Data Tools group to create data validation.

How to Create Dynamical Drop-Down List 9

Step 8: In Data Validation window, under Settings tab, under Allow dropdown list select ‘List’; Enter =SortList in Source. Then click OK.

How to Create Dynamical Drop-Down List 10

Step 9: Verify that dropdown list is created. Values are sorted by alphabetical order properly.

How to Create Dynamical Drop-Down List 11

Step 10: Add a new fruit ‘Longan’ into sheet1 original list. You can see that it is added into sheet2 list automatically with proper order, and it is also displayed in dropdown list properly.

Sheet1:                                    Sheet2:                                 Dropdown list:

How to Create Dynamical Drop-Down List 12                   How to Create Dynamical Drop-Down List 13                    How to Create Dynamical Drop-Down List 14

Related Functions


  • Excel INDEX function
    The Excel INDEX function returns a value from a table based on the index (row number and column number)The INDEX function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.The syntax of the INDEX function is as below:= INDEX (array, row_num,[column_num])…
  • Excel MATCH  function
    The Excel MATCH function search a value in an array and returns the position of that item.The MATCH function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.The syntax of the MATCH function is as below:= MATCH  (lookup_value, lookup_array, [match_type])….
  • Excel IF function
    The Excel IF function perform a logical test to return one value if the condition is TRUE and return another value if the condition is FALSE. The IF function is a build-in function in Microsoft Excel and it is categorized as a Logical Function.The syntax of the IF function is as below:= IF (condition, [true_value], [false_value])….
  • Excel ROW function
    The Excel ROW function returns the row number of a cell reference.The ROW function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.The syntax of the ROW function is as below:= ROW ([reference])….
  • Excel COUNTA function
    The Excel COUNTA function counts the number of cells that are not empty in a range. The syntax of the COUNTA function is as below:= COUNTA(value1, [value2],…)…
  • Excel COUNTIF function
    The Excel COUNTIF function will count the number of cells in a range that meet a given criteria. This function can be used to count the different kinds of cells with number, date, text values, blank, non-blanks, or containing specific characters.etc.= COUNTIF (range, criteria)…
  • Excel SMALL function
    The Excel SMALL function returns the smallest numeric value from the numbers that you provided. Or returns the smallest value in the array.The syntax of the SMALL function is as below:=SMALL(array,nth) …
Related Posts

Extract matching values From Two Lists
extract matching values from two list1

Suppose that you are working with two lists containing few values, and you want to extract the matching values from those two lists into another separate list. You might prefer to manually extract the matching values from the two lists, ...

Extract all Partial Matches
extract all partial matches1

Just assume that you have a range of data that contains a text string and you want to extract the few partial matches into another separate range of cells; then you might think that it's not a big deal; because ...

Extract Multiple Match Values into Separate Columns

If you have a few values/items in the excel sheet and you are thinking that with the aid of the “VlOOKUP” function you can look for a specific value, extract it and then put the matching item into the separate ...

How to Sum the Smallest N Values in Excel

Sometimes we may want to sum the first smallest N numbers in a range in Excel. In this article, we will show you the method of “SUM the Smallest N Numbers” by a simple formula which consist of SUMPRODUCT and ...

How to Find the Smallest Value and Smallest Positive Value in Excel
Find the Smallest Positive Value 9

Sometimes we want to find out the smallest positive value among a set of values. We should ignore the negative value and zero value when getting the smallest positive value. So we design different cases in this article to demonstrate ...

How to Get Row Number From a Vlookup in Excel
get row number from vlookup2

This post will guide you how to get row number from a Vlookup in Excel. How do I return a row number from a Vlookup with a formula in Excel. Assuming that you have a list of data in range ...

How to Get the First, 2nd or Nth Match Using Vlookup/Index/Match
Find the First Match Value Using VLOOKUP4

This post will guide you how get the nth matching values with VLOOKUP function in Excel. How do I find the nth match value with Index/Match formula in Excel. How to find the first, second, third or nth matching value ...

Ignoring Blank or Zero Cells with Conditional formatting

This post will guide you how to make conditional formatting ignore blank cells or zero cell in Excel. How do I force blank cells or zero cells to be ignored in conditional formatting in Excel. How to ignore blank cells ...

Find the Earliest and Latest Date in a Range of Dates in Excel
find earliest date4

This post will guide you how to find the earliest date in a range of dates in Excel. How do I get the earliest and latest date in a range with a formula in Excel. How to find the highest ...

Find Closest Value or Nearest Value in a Range in Excel
find closest value3

This post will guide you how to find the closest value or nearest value in a range of cells or in a column in Excel. How do I find the closest match in a range to a given lookup value ...

Sidebar