How to Create Drop down List in Excel

Dropdown lists are particularly useful when you want to restrict the type of values a user can input on a specific cell. Additionally, you can even display an error message when someone tries to enter a custom value.

You can create a dropdown in different ways; with a cell range, named range, table, or manually. No matter which method you use, all of them can be done using the Data Validation feature.

Once you create a dropdown list, a user can choose between different options by clicking the arrow icon on the cell’s right side. So, without further delay, let’s get into the nitty-gritty of how you can create one and edit the options once you create it.

How to Create a Drop-down List in Excel?

To create a drop-down, you need to have a list of values you want to display. For your convenience, you can even place the dropdown values in a separate worksheet and refer to cells from your worksheet.

Here’s how to do it.

1. Open the Excel worksheet. If you use Excel online, sign in to your Office 365 account.
2. Select the cell (s) where you want to have the dropdown menu.
   
3. Click Data Validation under the Data tab (inside Data tools)
   
4. Select the Settings tab, and choose the List option below the Allow field.
   
5. Use one of the following methods to choose dropdown values.
   • Manually enter values: This method is particularly useful when your list contains very few options. For instance, Yes/No, True/False, etc. 
     Here,  you manually enter the dropdown values separated by a comma. However, the options are case-sensitive. (Apple and apple are not the same things.)
     
   • Using a cell range: You can select a cell range to include all cells inside it under the dropdown options. Here, you click the Up arrow icon on the Data Validation window.
     
     Then, select the range of cells that already contains values you want to display and press Enter.
   • Using an Excel table: If you have the dropdown list values inside a table, select the cells inside it that you want to include as dropdown values. To convert a particular column into a table, select it and press Ctrl/Command + T.
     
     In this figure, you see Regions instead of cells because we named the cell range as Regions.
6. Click OK to close the Data Validation window.
7. To use the dropdown values, click the down arrow icon and select the preferred value from the list of options.
   

How to Edit the Dropdown List Options?

Once you have a dropdown list, you can add/remove options inside it anytime later. However, you should have access to the worksheet containing those values. Also, it depends on what type of dropdown list you are using.

For instance, if you are using a dropdown inside an Excel table, you can enter the new value at the end of the table, and the dropdown options are automatically updated. Likewise, if you want to delete an option, select the cell and press the Delete key.

On the other hand, you have to update the selection of the cells manually for the dropdown list using cell range or named range.

For Cell Range

1. To add a new dropdown value, right-click on the cell inside the list and select the Insert option.
   
2. Then, select Shift cells down on the Insert window prompt. You can also choose other options according to your preferences.
   
3. To remove a dropdown list option, select the cell and choose the Delete option.
4. Then, choose the List option under Allow and select the new cell range with the updated dropdown list options.
   

For Named Range

1. Add or delete the dropdown values similar to the cell range method above (Steps 1,2,3).
2. Now, select the worksheet that contains the dropdown list values.
   
3. Then, click Name Manager under the Formulas tab.
   
4. On the Name Manager prompt, click the small Up arrow icon under the Refers to field.
   
5. Select the cells you want to include as the new dropdown list values.
   
6. Click the Tick icon when done.

How to Display an Error Message if the User Enters an Invalid/Custom Value?

By default, Excel doesn’t display any error even if someone starts typing a custom value on the dropdown cell. However, you can alert them with an error message to prevent it.

1. Upon the Data Validation prompt, select the Error Alert tab.
2. Enable Show error alert after invalid data is entered checkbox.
3. Provide a title and error message you want to display upon custom input.
4. Choose one of the following options under the Style field.
   
   • Stop: It doesn’t allow the user to enter an invalid value. (only takes valid value).
   • Warning: It displays a warning prompt where users can choose to stick with the value they entered or re-enter a valid value.
   • Information: It displays the error message, but users can continue inputting any value they want.

Additionally,  you can pre-inform the user about the type of values they are expected to enter with the help of a display message.

1. Select the Input Message tab.
2. Tick Show input message when cell is selected checkbox.
3. Then, enter a title and the message you want to display when the user tries to enter a value.
   
4. Click OK.
5. Now, you get a little pop-up message when you hover over the cells with dropdown values.
   

Related Questions

How to Create a Searchable Drop-down List?

The dropdown is supposed to help the user input an option quickly. However, when you have too many options, you have to manually scroll to search and select the preferred option from the list.

Once you start typing on the searchable dropdown list, Excel autocompletes options that match or contain parts of your search keywords. Then, you simply select the correct one.

If you use Excel online, the dropdown is already made searchable. However, on the Desktop version, only a particular group of people who are part of the Office Insider beta channel have access to this feature as of now. For those who don’t have it, you need to create a formula with functions like FILTER and SEARCH to create a searchable dropdown list.