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. In an empty column that contains no other data, 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, 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

Note. In this example, cell A1 contains a heading that should not be included in the dynamic range. Therefore, we add -1 correction to the COUNTA function to prevent adding a blank at the end of the dropdown list. In case there is no other data above the referenced cell, use the COUNTA function without this correction, e.g.:

=OFFSET(Sheet3!$A$1, 0, 0, COUNTA(Sheet3!$A:$A), 1)

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.

Note. For a formula to work correctly, there should be no blank cells between data entries in the referenced column. If there is some other data above the topmost referenced cell like in the above example, then you should subtract the corresponding number of cells from COUNTA's result.

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)

106 comments

  1. For me the drawback of using a table as source is having to use the INDIRECT function with the table reference as string. If you change the name of your table or column, it basically will break the drop down. Is there a way around that?

    Also I would be looking for a way to update the table with the values from the drop down, if the value is changed in the source table. Is there a way to achieve that? I think basically when a value is chosen from the drop down, Excel would need to then insert a reference to that cell, instead of the value.

    1. Hello Robert!
      You can find a Excel table on a worksheet regardless of its name and get any column from it by its number using VBA.
      Excel drop-down list can only insert the selected text value into a cell.

  2. Very Nice article.
    I have a question.
    I need a dropdown like a list box but it lets you select either suggested contents or allows you to add one of your own. This can be multiple word wrapped lines (a large amount of text in one box)

    1. Hello Anthony!
      To allow Custom Entries:
      In the Data Validation dialog box, go to the Error Alert tab.
      Uncheck the Show error alert after invalid data is entered box. This will allow users to enter values not in the list.
      To enable Multi-line Text:
      To allow multi-line text, you can use a Combo Box from the Developer tab.
      Go to the Developer tab (if it’s not visible, you can enable it from Excel Options. Read more: How to add Developer tab in Excel).
      Click on Insert and select Combo Box (ActiveX Control).
      Draw the Combo Box on your worksheet.
      Right-click the Combo Box and select Properties.
      Set the MultiLine property to True.
      Set the EnterKeyBehavior property to True to allow new lines when pressing Enter.
      Link the Combo Box to a Cell:
      In the Properties window, set the LinkedCell property to the cell where you want the selected or entered value to appear.
      This setup will allow you to have a dropdown list that supports both selecting from predefined options and entering custom multi-line text.

  3. Very helpful site..! I'm fairly new to this but can't seem to find a solution to my Combo Box problem.
    I have a Spilled Array that populates using the FILTER() formula in Column K2 on a worksheet called 'LIST_DATA'.
    On a worksheet called 'MULTI_SEARCH' I have an ActiveX Combo Box called 'ComboBoxTags' that references Cell E12.
    I am wanting the Combo Box to display the Values shown in the Spilled Array at K2 as the user types, effectively narrowing the options based on user input.
    This works at the moment if I use a Named Range and refer to this Named Range in the Combo Boxes ListFillRange property. The only problem is the Combo Box doesn't seem to shrink down as the number of Values reduces, it stays the full height. Ideally, the Combo Box would shrink down to exclude the blank values as the text entered in the Combo Box changes, which I can't seem to get to update with the Spilled Array..?
    Any help would be greatly appreciated.
    Regards.

    1. Hi! Unfortunately, without having and seeing your data, I can't set up a Combo Box for you in your workbook.

  4. Hi I'm new to this and have picked up some great tips. I am struggling with a drop down menu, I can get the results of multiple choice, but results are displayed as a continuous line . How can i change this to show multiple lines the same as it is in the drop down?
    Any assistance welcome thanks

  5. Hi, is there any way to create 2 dropdown lists which are dependent on each other? I mean - second dropdown list has a different content depends on what will be chosen on the first one

  6. Hello! I've gotten further with your article than I have with others so I thank you for that. For the step Create drop-down from Excel table, I was able to do that and it worked as expected. However, I need to have more than one table. How do I add additional tables? Can you please provide what the formula would look like? This formula in data validation works perfectly for just one table....=INDIRECT("Table1[Grocery]") but again, need to add additional tables, if this is even possible. Thank you!!

    1. Hi! You can only create a drop-down list from one data source. Therefore, you cannot use data from two tables for a drop-down list. You can merge the data from two tables using the VSTACK function and use a reference to that range of cells to create a drop-down list.

      =VSTACK(INDIRECT("Table1[Ingredients]"),INDIRECT("Table2[Ingredients]"))

      I hope my advice will help you solve your task.

      1. hello
        how should i filter the column ingredients
        like blank
        i can't find a solution for this

  7. Hi, is it possible to have the source of a dropdown list be in 1 cell instead of a table. I have to restructure a sheet with tens to hundreds of dropdowns, if i put them all in different tables it becomes a mess. I'd like to make a table with all the sources of the dropdowns in 1 cell separated by a comma, in the same style as you'd input your data in the Data Validation window. Then i refer the source to this one cell instead of the table, I just don't know if it is even possible.

    This would make my life much easier, thank you in advance
    Edoe

      1. Okay, thank you

  8. For some reason, this isn't working the way I need it to work. I want to be able to select an option from the drop down list, add some data and then repeat it for each option of the drop down list. For example: I want to click on Birmingham from the drop down list, add information about Birmingham and then when I click on the drop down and choose a different location like London, I want the sheet to be blank and add information to it. When everything is complete, I want to be able to click on the drop down list for each location to see that the data changes.

    1. Hi! A drop-down list writes the selected value to a cell. But it cannot automatically change this value depending on some conditions. You can automatically remove values from cells when the value in a particular cell changes by using a VBA macro.

  9. Is it possible to create a drop down list where you can choose multiple entries? For example if one of the columns is food allergens, where you need to select multiple allergens from the drop down menu rather than just one.

  10. Great content! I'm in a bit of a pickle. Looking for a solution. How can i create a searchable drop down list in excel but appending to the data as one selects from the value. Today, my excel runs a macro that would allow one to select multiple drop down values using a (;) to separate the selection within any given one cell. My users however, are looking for a searchable drop down, but with no interruptions to the current macro that would allow again for multiple drop down selections using the semicolon as the separator for each selection.

    What can I use to satisfy this need?

  11. How to I copy a dynamic drop down list from the source data page to the actual worksheet? It doesn't copy over

  12. I've been using drop-down lists since the beginning of time, so I'm very familiar in setting this up and using it. Search in drop-down made my life so much easier, but recently it just stopped working - across all Excel sheets and workbooks that I open. I did not change anything - it is just not working - even if I use the mouse scroll button the scroll is not working, I have to drag down the little scroll bar in the box. I use Office 365, Windows Professional - I've done updates, restarted my laptop a thousand times, but no luck - does anyone know how I can fix this.

    1. Hi! Specify how you searched in drop down list: with an ActiveX element or with a VBA macro in an ordinary drop down list. If the second option, there is probably a problem with the macro.

  13. hi, pretty page but I do not found my problem...
    I have a dropdown list with 26 entry BUT in use I can see always the first 8 and I have to srcoll down all the times if I wants to use an entry. My Question: can I prolong the presentation-list that I can see more of my list ???
    many thanks and regards from germany

  14. Hi, great site!
    My question is if it is possible to edit the format of the drop-down box.
    It is now 8 rows and i like it to have about 20?

    Regrads,
    Frans

  15. Hello Svetlana,
    Congratulations on your articles and on what you or your loved ones are going through.
    Concerning the paragraph Excel data validation list based on a table, I don't understand why you have to go through a named range. Can't we link directly to Table[[#data],[col_name]]? Maybe by coupling with an Indirect function?
    Sincerely yours and congrat to all your team, the info you give is most valuable
    Al1

    1. Hello!

      You are absolutely right - the combination of INDIRECT and table references works nicely. I've updated the instructions, thank you!

  16. Hello Svetlana,

    Thank you for a great tutorial and your through analysis and step by step presentation. You also did a wonderful job to explain the different options and pros and cons of each.

    The only step that did not work for me was when I put the Table Name as the Data Source. The Database is in Sheet1, and the Table1 is in Sheet2 (column A, rows 2:30 with header on row 1).

    Your input will be much appreciated. Thank you.

    Joe

    1. Hi Joe,

      Thank you for your kind words about this article! You are right, the table method does not work in the current Excel version, though I can swear it did work back in 2014 when the tutorial was written.

      Anyway, you can use the following workaround:

      - After creating a table, create a named range for a column of data without including the heading cell. For example, you can define the name "Ingredients" that refers to =Table1[Ingredients].
      - And then, create a data validation list based on the name.

      This extra step may sound like a needless complication, but it does make your drop-down list expandable - when a new item is added to the table, it will appear in the list automatically.

      I will update this part of the tutorial ASAP. Thank you for drawing my attention to this! :)

      1. Hey Pretty..
        I need your little help.. for Data validation can you help me...

  17. hello svetlana. Is this article the Italian translation of your article?

    https:// excelacademy.it/6623/creare-un-elenco-discesa-excel/

    1. Hi Luke,

      I do not speak Italian, but the contents seem very much alike :)

  18. Another great article, thank you so much!

    I've been using data validation based on table columns as my standard method since quite a while. However, there's one thing that annoys me: You have to create a separate named range for the table column (as you cannot put the table column reference as source into the data validation directly). Let's say I call the named range "Dropdown" and it refers to "Table[Column]" and has workbook scope.

    Now, when I copy any sheet within the workbook a new instance of Dropdown is created automatically that has the new sheet as scope. I didn't find any way to avoid that. It even happens when I copy a sheet that does not use Dropdown at all.

    Do you know how that can be avoided?

    1. I have this problem to. A bug since I can delete those duplicate named ranges that have a worksheet scope and the validation list will still work since it's using the workbook scoped original named range.

  19. Hi. Please have a look at the first "drop-down via comma-separated" option. You mention in step 3 to use a comma (,), however this should be a semi-colon (;).
    Otherwise thanks for the great help.

    1. Hello Jeanette!
      The use of a semicolon or semicolon in formulas is determined by the regional settings of your Windows.

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

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

Post a comment



Thanks for your comment! Please note that all comments are pre-moderated, and off-topic ones may be deleted.
For faster help, please keep your question clear and concise. While we can't guarantee a reply to every question, we'll do our best to respond :)