Create drop down list in Excel: static, dynamic, editable, searchable

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. Excel drop down list

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:

  1. 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.
  2. On the Data tab, in the Data Tools group, click Data Validation.
  3. 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. Create a drop down list in Excel.

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:

  1. 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.
  2. Select the cell(s) that are to contain the list.
  3. On the ribbon, click the Data tab > Data Validation.
  4. 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. Make a drop-down menu from a range of cells

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.

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

  2. 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: Create a named range.

  3. Select the cells for the picklist - on the same sheet as the named range or in a different worksheet.
  4. 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.
    Make a drop down list from a named range.

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:

  1. Type the list items in a table or convert an existing range to a table using the Ctrl + T shortcut.
  2. Select the cell(s) where you wish to insert a dropdown.
  3. Open the Data Validation dialog window.
  4. Select List from the Allow drop-down box.
  5. 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]")

  6. When done, click OK.

For this example, we're making a dropdown menu from the column named Ingredients in Table1:

=INDIRECT("Table1[Ingredients]") Creating an expandable dynamic dropdown from an Excel table

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.

  1. Type the items for the drop down menu in separate cells.
  2. 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
    Name a formula for a dynamic drop-down list.
  3. With the formula name defined, create a dropdown based on a named range as usual.
Creating a dynamic dropdown list in Excel

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. A dynamic array formula to extract 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: Making a dynamic dropdown list in Excel 365

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.
Create a drop down list from another Excel sheet.

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:

  1. In the source workbook, create a named range for the source list, say Source_list. Create a named range in the source workbook.
  2. 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.

    Defined a name that references the source list.
  3. 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.
Creating a drop down list from another workbook.

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. Searchable drop down list in Excel 365

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. Add a message to be displayed when a drop-down list cell is clicked.

The resulting drop down list with message will look similar to this: Excel drop down list with message.

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:

  1. Open the Data Validation dialog window.
  2. On the Error Alert tab, uncheck the Show error alert after invalid data is entered box. Make an editable drop down list to allow other entries.

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:

  1. On the Error Alert tab, select the Show error alert after invalid data is entered option.
  2. 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.
Create an editable dropdown list with a message.

And here's an editable Excel dropdown list with a warning message in action: An editable drop down list with a warning message in Excel

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)

Table of contents

256 comments

  1. Hello,

    How do you keep drop down box in the shared workbook. It works well in the unshared mode.
    Thanks

  2. Is it possible to create a conditional based data validation and how?

  3. Hi
    If I want to be able to change chosen list item in a list, can i do this non-vbs?

  4. Hi,

    I want to create a drop down list that when selected shows both the item number and description but once selected would only show the item number.

    For example, I would select the 2 - Blue but once selected, the cell would only show 2.

    Drop Down List
    1 - Green
    2 - Blue
    3 - Yellow
    4 - Brown

    Please let me know if it is possible.

    Thanks,

    Betty

    • Hi, Betty,

      I believe you need a VBA code for your task. But I'm afraid we won't be able to help with that.
      I can only suggest you asking around Mr. Excel forum for it. They have a section there where they help with such codes.

      Sorry I can't assist you better.

  5. Perfect , Thanks a lot. now i feel like a genius.
    it worked well on office 2016.

  6. Hi,
    I am wondering if it is possible to use a drop down list which could be then linked to a column of data. For example, if from the drop down list i choose a value, for the next row I want to disallow the selected value from first row, or I want the selected values to disappear for the drop down list in the next rows?!
    Thanks in advance

  7. Hi Svetlana Cheusheva,

    This article is super easy and very good. I created the drop down option from Data Validation options and it works perfect - I don't have to enter the category again and again anymore.

    I have a question regarding drop down list and auto fill to a different cell. My excel sheet is as below:

    Column B= Date
    Column C= Store Name
    Column D= Cost in USD
    Column E= Purpose (This is where my drop down list is choosen from, the Validation criteria list is obtained from Column S (List has Rent, Grocery (Cell=T12), Utility etc)

    So, I would like the Grocery Cell (T12 in column S) to fill automatically and get the cost updated ( up to date cost). Such as below:

    Data--Store Name--Cost--Purpose
    07/01/2017--Walmart--$26.50-Grocery
    07/04/2017--Sams Club--$16.50-Grocery
    07/06/2017--Walmart--$26.25-Grocery
    07/10/2017--Costco--$6.50-Grocery
    07/11/2017--Walmart--$10.50-Grocery

    "Column S" & "Cell T12"
    After 07/01/2017 Entry - T12=$26.50
    After 07/04/2017 Entry - T12=$43.00
    After 07/06/2017 Entry - T12=$69.25
    After 07/10/2017 Entry - T12=$75.75
    After 07/11/2017 Entry - T12=$86.25

    Your help is very much appreciated and look forward to getting a result for this question.

    Thank you,
    Sam Arthur

    • Correction:

      "Column T" & "Cell T12" value to be updated.

      • Actually, I got the solution. Its just a formula to use instead of Vlookup. Thanks.

  8. 15 Nick Williams Jersey.Cheap 2014 New Cheap Jerseys Sale,
    MLB/NBA/NFL/NHL/NCAA Jerseys From China Free Shipping, NHL Jerseys, NBA Jerseys, inexpensive jerseys From factory.

  9. Using comma dont work for list, i had to use ";" in between the values.
    Ex:
    Yes;No;Maybe

    Did MS change this? I'm using 2013

  10. If I have an EXCEL drop down list and I want to enter free form text in one of the drop downs, and let it be part of the drop down?
    Example : I have static drop down messages in cells A1 to A5. If I wanted to enter free form text in cell A6?
    Thank you.
    Sumant

  11. the list in "Target Book" works only when the "Source Book" is open.
    how i solve it

  12. Thanks. Great article

  13. how to add multiple formulae drop down list in on the cell where we can choose sum, min, max or more function .please provide me solutions.
    like cell a is heading and a2:a20 id numeric data but we need a list of formula where we can choose sum, min, max or more function.

  14. I have created a dependent drop down list. But when I click on the drop down arrow, it shows the values in reverse order. My sheet has 1, 2, 3 in numerical order. But the drop down shows 3, 2, 1. Any way to fix this?

  15. Hi Svetlana

    is it possible to link a value to a drop down list ie oven cure 1 = 5hrs
    oven cure 2 = 3hrs so this can be intergrated into a cure schedule

  16. hello Svetlana

    from the drop down list if i select some, it should display in the every particular selected cell of the column, can this be done, thank you..

  17. Hi Svetlana,

    Using with drop down list(sheet1, Sheet2, Sheet3), is it possible to jump (active sheet) to another sheet as described in drop down list.

    Thanks
    VIJAY

  18. Hi There,

    This has been super helpful in creating my drop down menu... my only wish is that my employees could start typing an address and have it come up from the drop down menu? We have multiple addresses we need them to select from on a daily basis but I thought there may be a way for them to, for instance, type into the drop down menu (almost like a search, 1343 w_____ would bring up 1343 Whitby- from the drop down menu?

    We are trying to make it quicker and more accurate for myself when entering payroll. so that the guys stop making typo's on their addresses when entering their timesheets to which job they were working .

    I believe DEE (see above) is asking the same question.

    Thank you for your help!

    Lindsay

  19. Hi Svetlana,

    I want to make a list of name with drop down list but searchable. Can you help me on this? Thanks.

  20. HI,

    Thanks for the detailing about DropDown.
    I need to know, what if, I don't want to leave the cell Blank.
    I want my cell to display 'Select' before selecting the option from drop down.

    Please suggest.

  21. Hello,

    I was wondering is there a way to create your drop list where in the drop down it has the Ingredient and description of Ingredient but once I choose my option it just display the Ingredient and not the description. For example, in the drop list (when the arrow is clicked on) it has:

    Eggs - yellow yoke
    Broccoli - green vegetable

    but once I choose Eggs, it just display "Eggs" in the cell and not "yellow yoke".

    Is it possible?

  22. Hi, i need help on drop down list issue below : -

    I got a file from others, there is cell containing drop down list and i need help how to edit the item name inside the drop down list because i couldn't find where is the source. Tq

  23. This is very useful to my office works.
    Thanks a lot.

  24. Hello,

    Thank you for this great source of information!

    I would like a second list to populate information depending on the selection of the first list.

    For example in the first list column A we have male and female. Second list column B has Pregnancy Test and NA. So if user selects Male then the only available item in list B should be NA.

    I've looked at the IF formulas but am unable to find the correct combination.

    Thank you for your advice.

    Troy

  25. This is incredibly helpful. But is there a way for more than one item on the list to be selected? For your example of the ingredients - would someone be able to select both flour and eggs? Or would you suggest two columns both with the same drop down list?

    • Hello Roxy

      Excel's dropdown allows selecting only one item. So, in your case creating two drop down lists with the same items sounds quite reasonable.

  26. my drop down list contains the months of the year. when i click a different month can the body change to the corresponding month? I am keeping attendance. so when i click to december i want the record to start blank.

  27. Hello there, this is a great post! Thank you so much.
    I have a query - What if there are multiple entires of first Cell in the table with different dependant values?
    For Ex:
    Orange | Fruit | Tasty
    Oragne | Color | Good

    So the when I select first column as Orange, it should show up both Fruit and Color in second drop down.. but since you INDEX(), it only shows first match. (Fruit) .

    How to make multiple entries show up? Any help will be greatly appreciated.
    Again, thank you so much for the great blog.

  28. Excellent post!

  29. Hello, Svetlana
    Thank you, great blog if you could help with one more problem I would be grateful. I am trying to populate a data validation list based on a table that works as follows “Col A.” reps a State, “Col B.” City, “Column C.” Postal ZIP Code. No cells in table are blank i.e. A2:A5 = Mississippi then B2:B5 = Tupelo then C2 though C5 are different. Is there any way to make a Zip Code selection based on the City?
    The table has 9 States and 38 Cities and approximately 122 Zip Codes.
    Thank you again

  30. Hi, I have a dropdown list wherein it allows user to select their names that will reflect the time that they have finished doing the task. My question is, I want to prevent other users from selecting other people's name. in short, they should only select their own name. How will I do that?

  31. i need help. I have a combo boxes and I want to use it to be entry data into another worksheet serially. the post above is very simple and understandable. good work thanks

  32. Dear Svetlana Cheusheva

    This post is so nice and simple for me to learn how to do and useful for me to finish my work Thank a lot.
    Be successful in you work
    Best Wishes,
    Pilofito

  33. I am trying to create a worksheet with each column being a month of the year but then making that month into 4 weeks within a drop down box. I have done that but when I go to change the weeks, the data does not change with the box I choose. I am not sure how to fix this.
    Please Help
    Patricia

  34. Hi..the post is really useful.. i have one small question..what do i so if i have multiple words in my list..for eg: if i have to type full names?

    • Hello Swati,

      No problem, just type the names like usual with spaces in between in some range, and then create a drop-down list based on that range.

  35. THANK YOU!!!

  36. Your demonstration most helpful. Keep it up!!

  37. hi,
    i made a drop down list, but want to type the starting alphabet to select the item, but it is only working by pressing arrow.
    please help..

      • Thank you Alexander,
        Please speak any other option if it is not possible in standard excel drop down.

        • He has to write.

  38. OK I must be missing something.

    I have a data sheet with all my drop-downs built, unfortunately it only works on the data sheet. But on page where I need to use them on page One the old lists are still their. I want to remove the old lists and put the new list with/button on Pg 1.

    And I am forced to right click and choose from drop-down. Would like a button when I click on the field.

  39. Hi,
    I have created a yes no drop down cell but now I can't fill it with a colour.
    Can anyone help?
    TIA
    Carl

    • Go to cell style to change color.

  40. interesting and helpful

  41. Sorry can tell Me About make Attendance For summery list excel
    thank .

  42. Is there a way to have a drop-down that is list of text descriptions, but once selected it is an ordinal number? For example, "Don't know", "Limited", "Meets", "Exceeds" and puts 0,1,2,3 so I can add the values. I am using this to provide a ranking but the ranks have different descriptions for the different columns of criteria.

  43. Hi,

    Quite a thorough tutorial, but I am trying to create a list where i can jump to specific item using keyboard in a long list (e.g. In a list of 1000 names I have to select a name starting with "t", how can i do that without scrolling from mouse)

    Regards,

  44. MY COMBOBOX KEEPS PUTTING TEXT IN A NUMBER CELL AND CHANGING THE CELL TEXT AND NOT A NUMBER. Is there a fix for this? It is a activex combobox I have ZIP codes being pulled up in a INDEX & MATCH =INDEX(info,MATCH(E10,ZIP,0),6) E10 is where I drop the zip code in. If I type the zip code in it works perfect but if you I use the combobox it changes all my fields to N/A (city,state,etc). Each time I use the combobox to put the value in E10 I get a error symbol next to E10 "this cell is formatted as text" there is a drop down to convert it to a NUMBER and once I do that all the N/A in the fields goes away. PLEASE HELP and Thank You

  45. How to put shapes instead of txet or numbers in drop down list in Excel?
    Thank you,

  46. I have a list of actors and list of their respective films in a sheet, in another sheet I have the column Actors and Films column. The user selects an actor from the dropdown list (about 4 lines long). Then a film is chosen from a dropdown list (about 15 lines long) of films about the chosen actor.

    I would like that , when a title film is selected this title will not be seen next time the user choose the same actor, but should be available for the next time the file is open or the 15 lines run out.
    Ideally a message should flash like "That film has already been chosen, try another one". Any help will be much appreciated. Thank you

  47. I want to make a drop down list for example on D3 1 to 5 by entering just 5 on B2 cell.

  48. I have the drop down list working. But, is there any way to be able to start typing until the correct choice pops up, move on the the next drop down list.

  49. Hello!
    I create a drop down list for 10 column and then I want to add more column but I can't insert or delete the column. The only way I can do is drag it down to more make more column. SO what's the problem? and what can I do to insert column beside drag down?
    Thank you.

  50. Hi,
    Is it possible in Excel that the value list (drop-down list) shows like (Value Description) which are in two columns, but when user clicks the value, only value is selected in the destination cell

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)