by The tutorial shows how to make a dropdown list in Excel with simple steps: from a range of cells, named range, Excel table, another sheet. You will also learn how to make an Excel dropdown menu dynamic, editable and searchable.
Microsoft Excel is good at organizing and analyzing complex data. One of its most useful features is the ability to create dropdown menus, which allow users to select an item from a predefined list. Dropdowns can make data entry faster, more accurate and more consistent. This article will show you a few different ways to create a dropdown menu in Excel.
Excel drop down list
Excel drop down list, aka dropdown box or dropdown menu, is used to enter data in a spreadsheet from a predefined items list. When you select a cell containing the list, a small arrow appears next to the cell, so you click on it to make a selection.
The main purpose of using drop down lists in Excel is to limit the number of choices available for the user. Apart from that, a dropdown prevents spelling mistakes and makes data input faster and more consistent.
How to create drop down list in Excel
To make a drop-down list in Excel, use the Data Validation feature. Here are the steps:
- Select one or more cells where you want the picklist to appear. This can be a single cell, a range of cells, or a whole column. To select multiple non-contiguous cells, press and hold the Ctrl key.
- On the Data tab, in the Data Tools group, click Data Validation.
- On the Settings tab of the Data Validation dialog box, do the following:
- In the Allow box, select List.
- In the Source box, type the items separated by a comma with or without spaces. Or select a range of cells on the sheet containing the items.
- Make sure the In-cell dropdown box is checked (default), otherwise the drop-down arrow won't appear next to the cell.
- Select or clear the Ignore blank option depending on how you want to handle empty cells.
- When done, click OK.
Congratulations! You have successfully created a simple dropdown list in Excel. Now, your users can click an arrow next to a cell, and then select the entry they want.
A drop down list of comma separated values works well for small data validation lists that are unlikely to ever change. For frequently updated lists, you'd better use a range or table for the source. The detailed step-by-step instructions for each method follow below.
Tip. To expedite data input in your Excel sheets, you can also use a data entry form.
Make drop-down menu from a range of cells
To insert a drop-down list based on the values input in a range of cells, carry out these steps:
- Start by creating a list of items that you want to include in the drop-down. For this, just type each item in a separate cell. This can be done in the same worksheet as the dropdown list or in a different sheet.
- Select the cell(s) that are to contain the list.
- On the ribbon, click the Data tab > Data Validation.
- In the Data Validation dialog window, select List from the Allow drop-down menu. Place the cursor in the Source box and select the range of cells containing the items, or click the Collapse Dialog icon and then select the range. When done, click OK.
Advantages: You can modify your dropdown list by making changes in the referenced range without having to edit the data validation list itself.
Drawbacks: To add or remove items, you will need to update the Source range reference.
Insert drop down list from a named range
Initially, this method of creating an Excel data validation list takes a bit more time but may save even more time in the long run.
- Make a list of items on the sheet. The values should be entered into a single column or row without any blank cells.
Tip. It's a good idea to sort the items alphabetically or in a custom order you want them to appear in the drop-down menu.
- Create a named range. The fastest way is to select the cells and type the desired name directly in the Name Box. When finished, click Enter to save the newly created named range. For more information, please see how to define a name in Excel.
As an example, let's create a range named Ingredients:
- Select the cells for the picklist - on the same sheet as the named range or in a different worksheet.
- Open the Data Validation dialog window and configure the rule:
- In the Allow box, select List.
- In the Source box, type an equals sign followed by the range name. In our case, it's =Ingredients.
- Click OK.
Note. If your named range has at least one blank cell, leaving the Ignore blank box selected allows typing any value in the validated cell.
Advantages: If you insert multiple drop-downs in different sheets, named ranges will make them a lot easier to identify and manage.
Drawbacks: Takes a bit more time to set up.
Create drop-down from Excel table
Instead of using a named range, you can place the source data into a fully functional Excel table. Why may you want to use a table? First and foremost, because it lets you create an expandable dynamic drop-down list that updates automatically as you add or remove items to/from the table.
To make a dynamic dropdown from an Excel table, follow these steps:
- Type the list items in a table or convert an existing range to a table using the Ctrl + T shortcut.
- Select the cell(s) where you wish to insert a dropdown.
- Open the Data Validation dialog window.
- Select List from the Allow drop-down box.
- In the Source box, enter the formula referring to a specific column in your table, not including the header cell. For this, use the INDIRECT function with a structured reference like this:
=INDIRECT("Table_name[Column_name]") - When done, click OK.
For this example, we're making a dropdown menu from the column named Ingredients in Table1:
=INDIRECT("Table1[Ingredients]")
Advantages: Easy and quick way to insert an expandable dynamic drop down menu in Excel.
Drawbacks: Not found :)
How to create a dynamic dropdown list in Excel
If you regularly change the items in your picklist, the best approach is to create a dynamic drop down list. In this case, the list will update automatically in all the cells that contain whenever you add or remove items to/from the source list.
The fastest way to make a dynamic drop down in Excel is from a table as shown above. That is the default behavior of Excel tables; no extra settings or moves are required.
Another way is to use a regular named range and reference it with the OFFSET formula, as explained below.
- Type the items for the drop down menu in separate cells.
- Create a named formula. For this, press Ctrl + F3 to open the New Name dialog box. Type the name you want in the Name box, and then enter the following formula in the Refers to box.
=OFFSET(Sheet3!$A$2, 0, 0, COUNTA(Sheet3!$A:$A), 1)Where:
- Sheet3 - the sheet's name
- A - the column where the drop-down items are located
- $A$2 - the cell containing the first item
- With the formula name defined, create a dropdown based on a named range as usual.
How this formula works
The formula comprises two functions - OFFSET and COUNTA. The COUNTA function counts all non-blanks in the specified column. OFFSET uses that count for the height argument, so it returns a reference to a range that includes only non-empty cells, starting from the cell containing the first item that you supply for the reference argument.
Advantages: The main advantage of a dynamic drop-down list is that you won't have to change the reference to the named range each time the source list is expanded or contracted. You simply delete or type new entries in the source list, and your dropdown menu will update automatically!
Drawbacks: A bit complex setup process.
Make a dynamic dropdown list in Excel 365/2021
Dynamic Array Excel has many innovative functions that are not available in older versions. One of these new functions named UNIQUE can help you create a dynamic drop-down with a simple formula.
Suppose you have a dataset with many repeated items like in column A in the image below. You aim to add a dropdown list where each item appears just once.
To extract the unique items, use this formula:
=UNIQUE(A2:A21)
Optionally, you can sort the extracted values alphabetically by wrapping it in the SORT function:
=SORT(UNIQUE(A2:A21))
This dynamic array formula is entered just in one cell (E2) and it automatically spills into as many cells as needed to show all the unique items.
Next, you set up a drop down list using a spill range reference, which is a cell address followed by a hash character. In our case it's =$E$2# or =Sheet1!$E$2# if a dropdown is in another sheet:
The result is an expandable dynamic drop-down list - the UNIQUE function automatically extracts new items as they are added to the source table, and the spill range reference forces Excel to update the drop-down list accordingly.
Tip. The same approach can be used to create a cascading drop-down list in Excel 365. For full details, please see Make a dynamic dependent dropdown list an easy way.
How to create drop down list from another sheet
To insert a drop-down menu that pulls data from a different worksheet, you can use a normal range, named range or Excel table:
- When making a dropdown menu from a named range, make sure the scope of the name is Workbook, and then set up a data validation list as usual.
- When creating a drop down list from a table, no extra steps are needed as table names/references are valid across the entire workbook.
- If you insert a drop down from a regular range, include the sheet's name in the source reference. In the Data Validation dialog window, place the cursor in the Source box, switch to the other sheet and select the range containing the items. Excel will add the sheet name to the reference automatically.
How to make drop-down list from another workbook
To create a drop-down menu in Excel using a list from another workbook as the source, you will have to define 2 named ranges - one in the source workbook and the other in the workbook where you wish to insert your Data Validation list. The steps are:
- In the source workbook, create a named range for the source list, say Source_list.
- In the main workbook, define a name that references your source list. For this example, we create the name Items that refers to:
=SourceFile.xlsx!Source_list
If the workbook's name contains spaces or non-alphabetical characters, it must be enclosed in single quotation marks like this:
='Source File.xlsx'!Source_list
For more details, please see How to make external reference in Excel.
- In the main workbook, select the cell(s) for your picklist and click the Data tab > Data Validation. In the Source box, reference the name you created in step 2. In our case, it's =Items.
Notes:
- For the drop-down list from another workbook to work, the source workbook must be open.
- The dropdown list created in this way won't update automatically when items are added to or removed from the source list - you will have to modify the source list reference manually.
How to make a dynamic dropdown from another workbook
To create a dynamic dropdown list from another workbook, define a formula name in the source workbook using the OFFSET formula explained in Creating a dynamic drop-down in Excel. In this case, a dropdown menu in another workbook will be updated on the fly once any changes are made to the source list.
Searchable drop down list in Excel 365
In Excel 365, data validation lists have an awesome AutoComplete feature. To speed up data entry in large lists, just start typing the target word in the dropdown menu cell - the autocomplete algorithm will match the typed substring with the dropdown list items and show you the found matches. As you type more characters, the displayed list is narrowed down, and conversely, when you remove characters, more matches are shown.
Insert a drop down list with message
To show an information message when someone clicks a dropdown list cell, proceed in this way:
- In the Data Validation dialog box, switch to the Input Message tab.
- Make sure the Show input message when cell is selected option is checked.
- Type the title and message in the corresponding fields (up to 225 characters).
- Click OK to save the message and close the dialog.
The resulting drop down list with message will look similar to this:
Make an editable drop down list in Excel
By default, an Excel drop-down is non-editable, i.e. restricted to the values in the list itself. If you type any other value, an error alert will show up. However, you can allow users to enter their own values. Here's how:
- Open the Data Validation dialog window.
- On the Error Alert tab, uncheck the Show error alert after invalid data is entered box.
Technically, this turns a drop-down list into a combo box. The term "combo box" means an editable dropdown that allows users to either select a value from the predefined list or type a custom value directly in the box.
Optionally, you can display a warning message when someone attempts to enter a value that is not in the list:
- On the Error Alert tab, select the Show error alert after invalid data is entered option.
- From the Style box, pick either Information or Warning, and then type the title and message text.
- Information message is best to be used if there is nothing wrong with the user entering a custom value.
- Warning message will induce users to select an item from the drop-down box rather than enter their own data, though it does not prohibit it.
And here's an editable Excel dropdown list with a warning message in action:
Tip. If you are not sure what title or message text to type, you can leave the fields empty. In this case, Excel will display the default alert "This value does not match the data validation restrictions defined for this cell."
That's how to make a simple drop-down list in Excel. In the next article, we will explorer this topic further and learn how to insert a cascading (dependent) drop down list with conditional Data Validation. Please stay tuned and thank you for reading!
Practice workbook for download
Excel drop-down list - examples (.xlsx file)
258 comments
Hi,
I am looking for a requirement like I have a,a,b,c,d,c,d in a column and now I want the whole columns unique values to be as a dropdown/list in another cell.
Is it possible?
Thank you very much
Hi,
I have created four cloumn names called a,b,c and d.I set Data Validation by selecting the Allow field as LIST and Source field as Yes,NO,N/A for B column.
My quetion is here : Whenever I select "yes" from the drop down list in B column , it should show the Dialogue box as "Fill the coulmn C and D".
Could you please let me know how to create this in excel.
Hi Din,
We don't know a formula for this task. Most likely you need a VBA macro.
Hi,
I created a Dropdown list, and it worked out well. But when I save the sheet, close and open it again I don't see the dropdown lists I created, and I'm not able to figure it out why, Please help me out.
Hi Vicky,
Please specify the format you use to save your workbook. Is it xls, xlsx, csv?
Hello,
These are really great tips. But is there an easy way to make drop-down list drop when clicking or double-clicking anywhere in the cell, not just on the arrow?
Hello,
You need a special VBA macro that will spot when you click on a cell and open the drop-down list.
It is make some of schedule. for work or class. Thank you too much.
It is make some of schedule.
Many Many Thanks
Nice!!
Suppose I have different columns (say A,B,C) with different values for fixed fields (say X,Y,Z). How do I create a drop-down of columns A,B and C such that when I select A the corresponding values of X, Y and Z is displayed?
By far this is one of the most detailed drop down list tutorials.
For below scenario, can a drop down list be created depending on the first column selection?
For example
Column A Column B
1 Amy Amy
2 Bob Bob
3 Bob Bob_wife
4 Chris Chris
Provided Bob is selected, a drop list will show only "Bob, Bob_wife"
Much appreciated for your time.
I have made the drop downs and they work great. However you can still type in them how do I make it so you can only choose from the drop down?
Спасибо,Светлана,за подробную статью. По-русски не публикуетесь, где-нибудь. Буду признателен за ссылочку.
Добрый день, Сергей. Приятно знать, что к нам заглядывают и соотечественники :) У нас пока есть только английская версия сайта, и поэтому и все мои статьи тоже на английском. В перспективе возможно появится и русская версия, и тогда переведем самые популярные статьи.
I would like to know how to use a "clear" button for a dropdown menu. I have a dropdown menu that lists hockey players. But if I want to re-use this tab to select another group of players, it would be awesome if I could click one button to clear all tabs and revert to the top selection word "Forward". When you select a name from the drop down, it populates cells to the left of the drop down. When you scroll to the top of the drop down and click "Forward" or "Defensemen", it defaults the cells to the left back to normal (no name or team or salary number). I'm pretty sure I'll have to create a button and use a macro but not super knowledgeable on how to do this. Your help would be greatly appreciated.
Thanks very much.
I need to create a drop down list in below format
In Colom A1 drop down
1. Student Name
2. Teacher Name
1.if select colom A1 "Student Name" then colom B1: need to show 3 category like " School fee need to enter" or "Transport Fee need to enter" or "Food Fee .
or
2. if select colom A1 "Teacher Name" then colom B1: need to show 3 category like " Salary need to Enter" or "Allowance need to enter" .
Please help me
I need to create a drop down list in below format
1.if colom A1 "Student Name" then colom B1: need to show 3 category like " School fee need to enter" or "Transport Fee need to enter" or "Food Fee .
or
2. if colom A1 "Teacher Name" then colom B1: need to show 3 category like " Salary need to Enter" or "Allowance need to enter" .
Please help me
I need to create a drop down list in below format
if colom A1 "Student Name" then colom B1: need to show 3 category like " School fee" or "Transport Fee" or "Food Fee . Please help me
Hello,
First of all, thank you very much for this article.
My Question is : Can we give default name to cell instead of showing in via input message. eg. Select from Dropdown.
Hi
how to insert a check box as an option in a drop down list. Thanks in advance
Iam working in construction company make labour wages in excel we want to know formula how can we feed information like website can generate information by entering just personal number please send formula link Svetlana Cheusheva
Hi Svetlana,
I am using Excel 2007, but am not experienced. I have set up an Invoice Template on worksheet no.1, and have keyed client information (names and addresses) under worksheet no.2. However I would like to be able to type in a clients name in the Invoice billing area and have a dropdown list which shows my clients names and addresses from worksheet 2 and then click on a name and the invoice automatically prefils with this information. I just don't know where to start. I have tried looking everywhere for information on how to do this which is easy to understand, but have not had any luck
Please can you help me?
Many thanks, Michele F
Hi,
I am looking at having a simple "drop down box" with items to select (i know how to do this) and then if I made another "drop in box" and used the same list again, I want the "item" chosen in the first "Drop Down box" unable to be selected.
thanks,
Does not make any sense - these tutorials should be easy for everyone
Really helpful. I think now i dont need to search for any other website for excel help. Thanks
Hi
I want to have a drop down list of say Towns which contains data for each town, say like population, number of bars, etc
When i click on a town the data changes
How is this done?
Hay there,
i gotta a problem....
actually i wanna find out the name easily from drop down list by type first letter of that word by which it will be appeared the words those started with the letter i typed. kindly suggest me.
Hi Svetlana,
I am looking for the filter in excel like if we type any character which are in the dropdown list, that should auto populate in the below of the cell.
Like if you find any city in http://www.cleartrip.com. When you enter the 1st letter "D" to search for "New Delhi". It auto populates all the entries which are starts with "D".
If you could give me your mail id. I can send you the screenshot for better understanding.
Please help me with the solution.
Hi svetlana,
I appreciate you by given quick response to every user.Please help regarding dropdown list with some parameters(Dynamic chart),as i created successfully the list but parameters value also display on the graph but limits not automatically updated with the each parameters. Thanks in advance for your support.
Hi svetlana,
I'm trying to create a drop down list in a form of states.
I'd like it to be like an online form where as you type the letters in the box it automatically takes you to that state example: As I type o 3 times it takes me to oregon, the third state beginning with o.
Can that be done in excel or is that an html thing that only works online?
Thanks
Thank you so much...
I want to learn look up
Hello Yusuf,
You may find our Excel VLOOKUP tutorial useful.
My values in the drop-down menu don't match the values that appear in my worksheet. How do I make adjustments so I get what I expect? Thanks.
I'm developing a testing platform. I'm struggling to come up with a formula in Excel for the following tasks
Add Pass/Fail/Advisory dropdown list to the spreadsheet with some functionalities.
When Fail/Advisory selected from the dropdown list. The user have to have a few lines with the outcome results. All the results then should be linked to a different Excel file (report page) where the outcome can be displayed.
The report page needs to be capable of being saved independently of any other pages which it is pulling data in from so we can email the report.
I can't find formula to update Excel sheet with data from different spreadsheets. Could anyone help me out please.
Regards,
Will
Hi
I have 6 sheets in a workbook.. In the 1st sheet(home page) I need to create a Combo box list. The list contains the sheet names 1,2,3,4 & 5. When I select 1 from the combo box it should directly go to the respective sheet 1.
I don't know how to do this procedure using combo box. Can you help me in this.
Regards.
Palani
data Validation is not avalable to select how do i make it avalable?
Thanks
Good example
Hello, I just moved into a logistics position within my company and I am using sheets that employees that no longer work with the company made. Within one of those workbooks I am having an issue with a drop down box. The way it is set up is by choosing a certain customer's name, it will draw info from mulitple cells and pull that customer's entire address and fill in cells on a diffferent sheet within the workbook. The problem I came across is that the drop down box stopped at a certain row even though there is more rows to be pulled up. I have been able to add these rows, but not the entire data from the row. I keep getting error message stating "The list source must be a delimited list, or a reference to a single row or column."
The rows (customers name) that was set up prior to my taking the postion works and pulls all info from different columns. The rows that I have added to the drop down box will not pull all info from that cell and I cannot figure out how to do what I need.
Please assist. Thank you.
Is it possible to make a list in a list like:
The dropdown will be e.g. a, b, c, d, e. When you stand on e.g. "a" antoher dropdown menu will appear with e.g. add 1, add 2, add 3....
a
add 1
add 2
add 3
add 4
b
test 1
test 2
test 3
c
d
e
Thank you so much for the article- it was very clear! my question is this:
is there a way to make a drop down table? instead of clicking on a cell and making a list come down, is there a way to make an entire table come down? the cells in the table don't have to be clickable- just drop down.
thanks again for your amazing article!
-Sarah
Love this article.
Is there a way to have the drop down list items disappear as they are selected?
Hi,
I am just wondering if its possible to display the contents of the drop down list which is quite similar to the content I am entering to the cell, for example:
I am typing ERM1500-RPH which is one of the content of my data validation. and there are actually few more data which are quite similar to this. ex: ERM1700-RPH, ERM1800-RPH and etc. I am just wondering if I start to type "ERM" in the cell, is it possible that all the options that starts with "ERM" will appear in the drop down list options.(I hope someone can imagine what I'm trying to say.) lol...
Please help me with this. Thanks.
-shey-
Hi, I have this problem that keeps me entering duplicate Document Code, How can I prevent this? Data Validation is not functioning I use
= COUNTIF(A:A,A1)=1
Example
NT CP-PROC-xxx
*the code starts here
NT CP-PROC-001
NT CP-PROC-002
*and so on
The cells are merged into 12
pls help.
I mean document code starts here
Thank you for a very clear explanation about drop down...thank you thank you thank you....
Is it possible to create a drop down list in footer or headers? Thank You
Thank You
Great Lesson
Great help!!! very useful
hai i want to know about that dropdown list so that it wont pick a name twice. example analyst comes twice in drop down list it should not come please tell
thanks
Hello, Svetlana. The article is very good. I did drop down (answers to the questions) but now I don't know how I can calculate the number for each answer (ex: for "Yes" 10 poins, for "No" 5 poins etc.
Now I can't connect the answer cell and another cell in which I'd like to the the amount of points.
Thank you in advance for your answer.
thank you for this information on this website - wondering when creating a list, where do I go to allow the user to be able to check more than one option from the drop down.
I can't not drop down list next two sheet, so how can I do it?
Hello
I would like to add a message when a value of a drop down list is selected
is this possible.
Using MO 2013