How To Add Drop Down List In Excel Cell

This article will tell you how to add a drop-down list in excel cells. You can add a drop-down list in one excel cell or multiple excel cells at the same time.

1. How To Add Drop-Down List In Excel Cells Steps.

1. Select one cell or multiple excel cells.
2. Click the Data tab —> Data Tools group —> Data Validation item to open the data validation dialog window.
3. Click the Settings tab.
4. Select the item List from the Allow drop-down list.
5. Input the drop-down list items ( such as Apple, Microsoft, Google ) in the Source text box, and separate the items with a comma.
6. Click the Input Message tab.
7. Check the checkbox Show input message when cell is selected.
8. Input the text Select one item from the list in the Title input text box.
9. And input the text This excel cell contains a drop-down list, you can select one item from the list. in the Input message text area.
10. Click the Error Alert tab.
11. Check the checkbox Show error alert after invalid data is entered.
12. Select item Stop from the Style drop-down list.
13. Input text Invalid input in the Title text box.
14. Input text You should select one item from the drop-down list. in the Error message text area.
15. Click the OK button to save the data validation rule.
16. Now when you select the cell, it will show a yellow tip dialog with the title & message text that is inputted in the Input Message tab beside the cell.
17. And there will show a drop-down arrow after the cell.
18. When you click the drop-down arrow, it will list all the items that you have entered in the Data Validation dialog—> Settings tab —> Source input text box.
19. You can also input data in the excel cell, but if the data do not exist in the drop-down list, then when you press the enter key, it will pop up an alert dialog window and show the title & message you entered in the Data Validation dialog window —> Error Alert tab.
20. When you input the item data text directly in the Data Validation dialog —> Settings tab —> Source text box like above, the item input is case-sensitive.
21. So when you input the text google in the cell and press enter key, it will pop up an error alert dialog.

2. How To Use Other Cells Value To Add Drop Down List In Excel.

2.1 How To Reference Other Cells In The Excel List.

1. Suppose there are some data in cell range $A$1:$A$3, and you want to use the text value in those cells as the list items.
2. Then you can input the formula =$A$1:$A$3 in the Data Validation dialog window —> Settings tab —> Source input text box.
3. Please note that you can only use one row or one column of cell data as the list item, otherwise it will pop up error alert when you save the data validation dialog window.
4. If you use other excel cells value to create the list like in this example, the item data is case-insensitive.
5. That means both the word GOOGLE and google are all valid entries in the cell.

2.2 How To Change List Items By Changing The Source Excel Cell Values.

1. In this case, when you want to change the items of the drop-down list, you only need to change the values in the source excel cells.
2. For example, you can right-click a cell in cell range $A$1:$A$3 and click the item Insert… in the popup menu list.
3. Then select the radio button Shift cells down in the pop-up Insert dialog and click the OK button.
4. It will create a new cell above the current cell, you can input the new list item’s text such as Python in the cell.
5. Then you can find the new item text when you click the drop-down arrow beside the list cell.
6. If you open the Data Validation dialog now, you can find the formula in the Settings tab —> Source input text box has also been changed to include the newly added cell in it automatically.

2.3 Create Excel List Items Best Practice.

1. You can create a separate worksheet and name the worksheet as ListOptionsSheet.
2. Add the list items data in the cells of one row or column in the worksheet.
3. Then right-click the worksheet tab at the excel bottom, and click the Hide menu item to hide it.
4. And input the formula =ListOptionsSheet!$A$1:$A$3 in the Data Validation dialog —> Settings tab —> Source input text box.

3. How To Allow Enter Text That Does Not Exist In The List Items.

1. If you want to allow users to input data that do not exist in the list, you can follow these steps.
2. Open the Data Validation dialog window.
3. Click the Error Alert tab and then uncheck the checkbox Show error alert after invalid data is entered.