Buzz
Today, Mytour will guide you on using a computer to create a drop-down list in a Microsoft Excel worksheet. This feature allows you to generate a list of selectable items and insert a drop-down selector into any blank cell on the sheet. The drop-down list functionality is only available on the desktop version of Excel.
Steps
Create a List
Open the Excel worksheet file you wish to edit. You can locate and double-click on a saved Excel file on your computer, or open Microsoft Excel and create a new spreadsheet.
Enter the values for the drop-down list. You need to input each drop-down item into separate, consecutive cells within the same column.
- For example, if you want the drop-down list to include "New York", "Boston", and "Los Angeles", you can type "New York" in cell A1, "Boston" in cell A2, and "Los Angeles" in cell A3.
Click on the empty cell where you want to insert the drop-down list. You can add the drop-down list to any blank cell on the worksheet.
Click on the Data tab in the toolbar ribbon. This button is located at the top of the worksheet. Data-related tools will appear.
Click on the Data Validation button in the "Data" toolbar. This button features two separate cell icons with a green checkmark and a red prohibition sign. A new dialog box will appear.
Click on the Allow drop-down menu in the "Data Validation" dialog box. This menu is located under the "Settings" tab in the dialog box.
- The Data Validation dialog box will automatically open to the Settings tab.
Select List from the "Allow" drop-down menu. This option will enable you to create a list within the selected blank cell.
In-cell dropdown
Ignore blank- If the drop-down list you are creating is a mandatory field, ensure this option is not checked. Alternatively, you can leave it unchecked.
Click on the text box below the "Source" section in the pop-up dialog box. Here, you can choose the list of values you want to insert into the drop-down list.
Select the list of values for the drop-down menu on the worksheet. Use your mouse to highlight the range of values you wish to include in the drop-down list.
- For instance, if you have "New York", "Boston", and "Los Angeles" in cells A1, A2, and A3, you should select the range from A1 to A3.
- Alternatively, you can manually enter the drop-down list values into the "Source" box. Ensure each item is separated by a comma in this case.
Customize List Properties
Click on the Input Message tab at the top of the "Data Validation" dialog box. This tab allows you to create a pop-up message that appears next to the drop-down list.
Show input message...- If you prefer not to display a pop-up message, leave this option unchecked.
Enter a "Title" and "Input Message." This section allows you to provide explanations, descriptions, or additional details about the drop-down list.
- The title and input message you type here will appear in a small yellow pop-up next to the drop-down when the cell containing the list is selected.
Click on the Error Alert tab at the top of the dialog box. This tab enables an error message to pop up whenever invalid data is entered into the drop-down cell.
Show error alert...- If you do not want an error message to appear, leave this option unchecked.
Select the error style from the Style drop-down menu. You can choose from Stop, Warning, and Information.
- The Stop option displays a pop-up with an error message, preventing users from entering data not in the drop-down list.
- The Warning and Information options do not block invalid data entry but show an error message with a yellow "!" or a blue "i" icon.
Enter custom "Title" and "Error message" (optional). The custom title and error message will appear when invalid data is entered into the drop-down cell.
- You can leave these fields blank. In that case, the default title and error message will follow Microsoft Excel's generic error template.
- The default error template has the title "Microsoft Excel" and the message "The value you entered is not valid. A user has restricted values that can be entered into this cell."
Click the OK button in the "Data Validation" dialog box. The drop-down list will be created and inserted into the selected cell.
Tips
- After creating the drop-down list, open it to ensure all the items you entered are displayed correctly. In some cases, you may need to expand the cell to show all items.
- When entering items for the list, input them in the order you want them to appear in the drop-down menu. For example, you can arrange the data alphabetically to make it easier for users to find specific items or values.
Warning
- You will not be able to access the "Data Validation" menu if the worksheet is protected or shared. In such cases, you need to remove the protection or unshare the document, then try accessing the Data Validation menu again.
