## Create a drop down list containing alphabetically sorted values

*Article updated on February 25, 2018*

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

An Excel table allows you to easily sort, filter and sum values in a data set where values are related.

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

A dynamic named range grows automatically when new values are added and also shrinks if values are deleted. This saves […]

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

The array formula in cell D3 extracts unique distinct values sorted A to Z, from column B to column D. […]

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

Unique distinct list sorted alphabetically based on a condition

The array formula in cell E6 filters values in column C based on value in cell E3, the output is […]

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

How to use the OFFSET function

In this post I am going to provide some basic examples to demonstrate how the OFFSET function works. I also […]

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

How to use a Table name in Data Validation Lists and Conditional Formatting formulas

David Hager gave this valuable comment about how to reference a table name in conditional formatting formulas: =INDIRECT("Table1[Start]") Watch this video to […]

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

Create dependent drop down lists containing unique distinct values

This article explains how to build dependent drop down lists. Here is a list of order numbers and products. We […]How to create a dynamic pivot table and refresh automatically

David Hager commented: Looks like an easy pivot table solution to me. Thanks for your comment! Now i know how to […]Create dependent drop down lists containing unique distinct values

This article explains how to build dependent drop down lists. Here is a list of order numbers and products. We […]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 […]How to use a Table name in Data Validation Lists and Conditional Formatting formulas

David Hager gave this valuable comment about how to reference a table name in conditional formatting formulas: =INDIRECT("Table1[Start]") Watch this video to […]### 15 Responses to “Create a drop down list containing alphabetically sorted values”

### Leave a Reply

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

<code>your formula</code>

Remember to convert less than and larger than signs to html character entities before you post your comment.

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