Create a drop down list containing alphabetically sorted values
Author: Oscar Cronquist Article last updated on January 05, 2019
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.
- Press with left mouse button on "Formulas" tab
- Press with left mouse button on "Name Manager"
- Press with left mouse button on "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.
- Press with left mouse button on "Close" button
Want to learn more about named ranges? Read this:
Recommended articles
Create a dynamic named range
A dynamic named range grows automatically when new values are added and also shrinks if values are deleted. This saves […]
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:
Recommended articles
Extract a unique distinct list sorted from A to Z
The array formula in cell D3 extracts unique distinct values sorted A to Z, from column B to column D. […]
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:
Recommended articles
Unique distinct list sorted alphabetically based on a condition
This article demonstrates how to extract unique distinct values based on a condition and also sorted from A to z. […]
This article demonstrates how to extract unique distinct values based on a condition and also sorted from A to z. […]
Create a dynamic named range "SortedValues"
- Press with left mouse button on "Formulas" tab
- Press with left mouse button on "Name Manager"
- Press with left mouse button on "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.
- Press with left mouse button on "Close" button
Learn more about the function OFFSET function:
Recommended articles
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 […]
The OFFSET function returns a reference to a range that is a given number of rows and columns from a […]
How to create a drop down list containing dynamic values
- Press with left mouse button on Data tab
- Press with left mouse button on Data validation button
- Press with left mouse button on "Data validation..."
- Select List in the "Allow:" window. See picture below.
- Type
=SortedValues
in the "Source:" window
- Press with left mouse button on OK!
If you are using an excel defined table, read this:
Recommended articles
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 […]
This article demonstrates different ways to reference an Excel defined Table in a drop-down list and Conditional Formatting. There are […]
Drop down lists category
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 […]
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 […]
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. […]
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 […]
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 […]
This article demonstrates how to set up dependent drop-down lists in multiple cells. The drop-down lists are populated based on […]
Run a Macro from a Drop Down list [VBA]
This article demonstrates how to run a VBA macro using a Drop Down list. The Drop Down list contains two […]
This article demonstrates how to run a VBA macro using a Drop Down list. The Drop Down list contains two […]
Change chart data range using a Drop Down List [VBA]
In this article I will demonstrate how to quickly change chart data range utilizing a combobox (drop-down list). The above […]
In this article I will demonstrate how to quickly change chart data range utilizing a combobox (drop-down list). The above […]
Change PivotTable data source using a drop-down list
In this article, I am going to show you how to quickly change Pivot Table data source using a drop-down […]
In this article, I am going to show you how to quickly change Pivot Table data source using a drop-down […]
How to automatically add new items to a drop down list
A drop-down list in Excel prevents a user from entering an invalid value in a cell. Entering a value that […]
A drop-down list in Excel prevents a user from entering an invalid value in a cell. Entering a value that […]
Create dependent drop down lists containing unique distinct values in multiple rows
Sharmila asks: How can i use these list for multiple rows? I would like to use these lists for multiple […]
Sharmila asks: How can i use these list for multiple rows? I would like to use these lists for multiple […]
Invoice template with dependent drop down lists
This article demonstrates a basic invoice template I created. It lets you use dropdown lists to quickly select products on […]
This article demonstrates a basic invoice template I created. It lets you use dropdown lists to quickly select products on […]
Use a drop down list to search and return multiple values
I will in this article demonstrate how to use a value from a drop-down list and use it to do […]
I will in this article demonstrate how to use a value from a drop-down list and use it to do […]
Compare data in an Excel chart using drop down lists
I will in this article demonstrate how to set up two drop down lists linked to an Excel chart, the […]
I will in this article demonstrate how to set up two drop down lists linked to an Excel chart, the […]
Use a drop down list to display data in worksheet header [VBA]
Aynsley Wall asks: I have a spreadsheet that I use for 3 different companies. What I would really like to […]
Aynsley Wall asks: I have a spreadsheet that I use for 3 different companies. What I would really like to […]
How to build an interactive map in Excel
This article describes how to create a map in Excel, the map is an x y scatter chart with an […]
This article describes how to create a map in Excel, the map is an x y scatter chart with an […]
Dependent drop down lists – Enable/Disable selection filter
Josh asks: I have this working right now with 6 drop downs/lists. I wanted to see if you possibly know […]
Josh asks: I have this working right now with 6 drop downs/lists. I wanted to see if you possibly know […]
Populate drop down list with filtered Excel Table values
This article demonstrates how to populate a drop down list with filtered values from an Excel defined Table. The animated […]
This article demonstrates how to populate a drop down list with filtered values from an Excel defined Table. The animated […]
Use a drop down list to filter and concatenate unique distinct values
Question: Is there a way to have a unique list generated from a list? Meaning I have a sheet that […]
Question: Is there a way to have a unique list generated from a list? Meaning I have a sheet that […]
Create a drop down calendar
The drop down calendar in the image above uses a "calculation" sheet and a named range. You can copy the drop-down […]
The drop down calendar in the image above uses a "calculation" sheet and a named range. You can copy the drop-down […]
Functions in this article
More than 1300 Excel formulas
Excel categories
Latest updated articles.
More than 300 Excel functions with detailed information including syntax, arguments, return values, and examples for most of the functions used in Excel formulas.
More than 1300 formulas organized in subcategories.
Excel Tables simplifies your work with data, adding or removing data, filtering, totals, sorting, enhance readability using cell formatting, cell references, formulas, and more.
Allows you to filter data based on selected value , a given text, or other criteria. It also lets you filter existing data or move filtered values to a new location.
Lets you control what a user can type into a cell. It allows you to specifiy conditions and show a custom message if entered data is not valid.
Lets the user work more efficiently by showing a list that the user can select a value from. This lets you control what is shown in the list and is faster than typing into a cell.
Lets you name one or more cells, this makes it easier to find cells using the Name box, read and understand formulas containing names instead of cell references.
The Excel Solver is a free add-in that uses objective cells, constraints based on formulas on a worksheet to perform what-if analysis and other decision problems like permutations and combinations.
An Excel feature that lets you visualize data in a graph.
Format cells or cell values based a condition or criteria, there a multiple built-in Conditional Formatting tools you can use or use a custom-made conditional formatting formula.
Lets you quickly summarize vast amounts of data in a very user-friendly way. This powerful Excel feature lets you then analyze, organize and categorize important data efficiently.
VBA stands for Visual Basic for Applications and is a computer programming language developed by Microsoft, it allows you to automate time-consuming tasks and create custom functions.
A program or subroutine built in VBA that anyone can create. Use the macro-recorder to quickly create your own VBA macros.
UDF stands for User Defined Functions and is custom built functions anyone can create.
A list of all published articles.
17 Responses to “Create a drop down list containing alphabetically sorted values”
Leave a Reply
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.
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/