## Create a drop down list containing alphabetically sorted values in excel

### Overview

This article describes how to create a drop down list populated with sorted values from A to Z. The sorted list is dynamic and it adds new values as you type them.

Here is a list containing some random values:

**Create a dynamic named range "List"
**

- Click "Formulas" tab
- Click "Name Manager"
- Click "New..."
- Type a name. I named it "List". (See attached file at the end of this post)
- Type
=OFFSET(Sheet1!$A$1, 0, 0, COUNTA(Sheet1!$A$1:$A$1001))
in "Refers to:" field.

- Click "Close" button

### Create a dynamic list sorted from A to Z

- Select Sheet2
- Select cell A1
- Type
=IF(COUNTA(List)>=ROWS($A$2:A2), INDEX(List, MATCH(SMALL(COUNTIF(List, "<"&List), ROW(A1)), COUNTIF(List, "<"&List), 0)), "")
**+ CTRL + SHIFT + ENTER** - Copy cell A2 and paste it down as far as needed.

**Create a dynamic named range "SortedValues"
**

- Click "Formulas" tab
- Click "Name Manager"
- Click "New..."
- Type a name. I named it "SortedValues". (See attached file at the end of this post)
- Type
=OFFSET(Sheet2!$A$1, 0, 0, COUNTA(Sheet2!$A$1:$A$1001))
in "Refers to:" field.

- Click "Close" button

**How to create a drop down list containing dynamic values**

- Click Data tab
- Click Data validation button
- Click "Data validation..."
- Select List in the "Allow:" window. See picture below.
- Type
=SortedValues
in the "Source:" window

- Click OK!

**Download example workbook**

Download excel sample file for this tutorial.

Create a drop down list containing alphabetically sorted values.xls

(Excel 97-2003 Workbook *.xls)

**Related articles:**

Create a drop down list containing only unique distinct alphabetically sorted text values using excel array formula

Create dependent drop down lists containing unique distinct values in excel

**Functions in this article:**

**IF(**logical_test;[value_if:true];[value_if_false]**)
**Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

**INDEX(**array,row_num,[column_num]**)**

Returns a value or reference of the cell at the intersection of a particular row and column, in a given range

**OFFSET(**reference,rows,cols, [height],[width]**)**

Returns a reference to a range that is a given number of rows and columns from a given reference

**COUNTIF(**range,criteria**)**

Counts the number of cells within a range that meet the given condition

**COUNTA(**value1,[value2]**,)
**Counts the number of cells in a range that are not empty

**ROW(**reference**)** Returns the rownumber of a reference

**ROWS(**array**)** returns the number of rows in a reference or an array

### 13 Responses to “Create a drop down list containing alphabetically sorted values in excel”

### Leave a Reply

**How to add a formula to your comment:**

<code>your formula</code>

**How to add VBA code to your comment:**

[vb 1="vbnet" language=","]

VBA code

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org

Add picture link to comment.

**Contact Oscar**

You can contact me through this webpage

Thank you for providing this! This is awesome. I did run into an issue though which I thought you might be able to help.

Whenever I save the workbook and reopen, the drop down only disaplays first two items. All the formulas are there, and I have to go into the Data Validation for each drop down and (without doing anything) just save it to re-enable the drop down.

I dug through google and didn't find much except that there is a known bug(?) which Microsoft is releasing a fix. Have you run into this issue? I do have rather large drop down (about 380) - could that be an issue?

Thank you very much for all your help!

Please disregard above question. I used the Defined Names, and the problem is resolved. Thank you again!

WJ,

Thank you for posting the solution!

Thanks so much for this-- it's been really helpful and worked perfectly for my lists with straight text entered.

I was also hoping to do the same with a list where cell values are based on CONCATENATE (from three other cells, two with names and one with a date in order to generate a unique name). I'm assuming that the above method doesn't deal with cells with forumulae (I'm returning a list of blank cells).

Is there any way for me to create an alphebetized list based on the values of these cells?

Hi there,

Kindly help me to get rid of few iss

[…] Perhaps there is a better solution, but if sorting the data is not an option, you may need to create a 2nd, automatically updated list, based on the first one - that also automatically sorts alphabetically. See example given in the link below Create a drop down list containing alphabetically sorted values in excel | Get Digital Help - Micros… […]

I am having trouble making the code reference different cells. My lists are not in column A and do not start at Row 1. What values in the code do I change to reference different cells?

Sometime when I paste the code for your saple to my new file, it will not function. Do you have to turn on or enable something, or is there a glitch in MS Excel program.

I think this formula is really cool. I used it in my excel tool and works great.

Does this not work for lists with both alphabets and numbers?

Seems to only work for lists that are either all words or all numbers.

This is a great formula but I have hit a problem with Excel 2011.

I have a list which will be constantly added to and I've used your formula to duplicate the list and sort it alphabetically. Then I have used your solution to create a data validation drop down menu from the sorted list. The drop down not only shows the entered values in my list but also shows the blank cells which have yet to be populated. How do we remove the blank cells from the drop down without changing the range of the drop down.

Thank you for the formula, it helps a lot! However,I have the same problem as Dan above. Do you have any suggestions on how to remove the cells with no text("blank")from the drop down list?

Any chance someone can tell me how to convert

=IF(COUNTA(List)>=ROWS($A$2:A2), INDEX(List, MATCH(SMALL(COUNTIF(List, "<"&List), ROW(A1)), COUNTIF(List, "<"&List), 0)), "")

to a standard formula? I cannot use array formulas in a shared workbook

Oscar:

I was fortunate to come across your page describing a method of creating sorted, dynamic data validation lists for Excel. The implementation was straight-forward and I was able to make it work in one instance on my workbook.

Unfortunately a VERY similar instance within the same workbook refuses to function, and I've been unable to determine why. In the latter case the "List" range works as expected, and the array formula returns the correct NUMBER of rows, but the values are either being repeated or truncated.

Any assistance will be appreciated. I am able to share the workbook with you if interested.

Matt