Create a drop down list containing alphabetically sorted values
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 allow 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.
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. […]
Create a unique distinct alphabetically sorted list
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 […]
Unique distinct list sorted alphabetically based on a condition
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
The OFFSET function returns a reference to a range that is a given number of rows and columns from a […]
How to use the OFFSET function
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 an Excel Table name in Data Validation Lists and Conditional Formatting formulas
This article demonstrates different ways to reference an Excel defined Table in a drop-down list and Conditional Formatting. There are […]
How to use an Excel Table name in Data Validation Lists and Conditional Formatting formulas
Question: How do I create a chart that dynamically adds the values, as i type them on the worksheet? Answer: […]
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
This article shows you how to refresh a pivot table automatically using a small VBA macro. If you add or delete […]
A dynamic named range grows automatically when new values are added and also shrinks if values are deleted. This saves […]
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 […]
Populate drop down list with unique distinct values sorted from A to Z
Question: How do I create a drop-down list with unique distinct alphabetically sorted values? Table of contents Sort values using […]
Apply dependent combo box selections to a filter
Josh asks: now if i only knew how to apply these dependent dropdown selections to a filter, i'd be set. […]
How to use an Excel Table name in Data Validation Lists and Conditional Formatting formulas
This article demonstrates different ways to reference an Excel defined Table in a drop-down list and Conditional Formatting. There are […]
Dependent drop-down lists in multiple rows
This article demonstrates how to set up dependent drop-down lists in multiple cells. The drop-down lists are populated based on […]
17 Responses to “Create a drop down list containing alphabetically sorted values”
Leave a Reply to Stack
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
English
Spanish
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
Hi, Oscar:
Excellent way to do the job. But it requires an extra list(or table) on the same sheet or other sheets (even other Workbooks. Is there a way to accomplish the same task without using extra list(or table)? (Or just Mission Impossible. Maybe VBA can do it.)
Thank you very much!
JOE
Yes, you can use a user defined function to sort the values and then display them in a regular drop-down list, this article demonstrates that it is possible:
https://www.get-digital-help.com/2012/08/27/use-filtered-table-values-in-a-drop-down-list-vba/
or this macro:
https://www.get-digital-help.com/2011/10/05/add-values-to-a-data-validation-list-vba/
I believe this article has a workbook that contains a macro or udf that sorts values:
https://www.get-digital-help.com/2011/04/21/excel-udf-combine-cell-ranges-into-a-single-range-while-eliminating-blanks/