## 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.

The picture below shows a list containing some random values.

First we need to build a cell reference that expands when we add values to the list, named ranges allows us to do that. However, if you own excel 2007 or a later version I highly recommend using an excel defined table instead. It has a built-in dynamic cell reference system, named structured referencing. You can read more about that here:

**Become more productive – Learn Excel Defined Tables**

Comments(0) Filed in category: Excel, Excel table

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

The following instructions tells you how to build an automatically expanding cell range.

- 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

Want to learn more about named ranges? Read this:

**Create a dynamic named range in excel**

Comments(12) Filed in category: Excel, Named range

### 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.

This article explains how to extract a unique distinct list sorted alphabetically:

**Create a unique distinct alphabetically sorted list extracted from a column**

Comments(53) Filed in category: Excel

You can even use a condition if you prefer, read more here:

**Unique distinct list sorted alphabetically and based on a condition**

Comments(1) Filed in category: Excel, VLOOKUP and return multiple values

**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

Learn more about the function OFFSET function:

Comments(2) Filed in category: Excel

**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!

If you are using an excel defined table, read this:

Comments(30) Filed in category: Data validation, Drop down lists, Excel, Excel table

**Download example workbook**

Create a drop down list containing alphabetically sorted values.xls

(Excel 97-2003 Workbook *.xls)

### 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

### Category: Drop down lists

Create dependent drop down lists containing unique distinct values

Here is a list of order numbers and products. We are going to create two drop-down lists. The first drop […]Comments(113) Filed in category: Dependent drop down lists, Excel

Create a drop down list containing only unique distinct alphabetically sorted text values

Question: How do I create a drop-down list with unique distinct alphabetically sorted values? Table of contents Sort values using […]Comments(76) Filed in category: Drop down lists, Excel

Apply dependent combo box selections to a filter in excel 2007

Josh asks: now if i only knew how to apply these dependent dropdown selections to a filter, i'd be set. […]Comments(42) Filed in category: Combobox, Dependent drop down lists, Excel

Comments(30) Filed in category: Data validation, Drop down lists, Excel, Excel table

Dependent data validation lists in multiple rows

This post describes how to setup data validation lists in a column and dependent data validation lists in an adjacent […]Comments(29) Filed in category: Dependent drop down lists, Excel

Change chart data range using a drop down list (vba)

This blog post demonstrates how to quickly change chart data range. I have created a drop down list (form control) […]Comments(22) Filed in category: Charts, Drop down lists, Excel, Interactive

Change pivot table data source using a drop down list

In this excel 2007 tutorial I am going to show you how to quickly change pivot table data source using […]Comments(18) Filed in category: Combobox, Drop down lists, Excel, Pivot table

Run a macro from a drop down list (vba)

You can quickly run a macro using a drop down list. The drop down list may contain multiple macro names. […]Comments(17) Filed in category: Drop down lists, Excel, Macro

Use a drop down list to search and return multiple values

Ainslie asks: I have multiple worksheets in an excel book. I have a drop down menu on the worksheet entitles […]Comments(12) Filed in category: Drop down lists, Excel, VLOOKUP and return multiple values

### 15 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

Hello Oscar,

I was very happy whenI saw your solution for an issue I face regularly. However...when I enter my data in your example worksheet, it doesn't function anymore. My data looks like '6000748, '6000845, '14000456 etc. So data is entered as text, like your data is. Neither does it function when I use your formulas and name definitions in my worksheet.

Do you have any idea where it goes wrong?

Kind regards,

Rens

Hello again,

I found out that the formula works when I precede the data with a letter, so 'D6000748, 'D6000845 etc. For now I have build a workaround in which I use three columns (add "D", Sort, remove "D") instead of one.

I suppose this might have the same cause as the problem I often experience in Excel when I have to Vlookup numbers stored as text. For that issue I also haven't found a real solution yet.

Regards,

Rens