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

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:

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.

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.

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

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:

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

   
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.

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:

1. Open the Data Validation dialog window.
2. 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:

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.

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

Practice workbook for download

Excel drop-down list - examples (.xlsx file)