Enter each option in a column (or row), one option per cell. Then go back to the worksheet with your data.
Enable data validation for selected cells
For this example, we want to add dropdown lists to the Rating or column B.. Select the cells you want to add the dropdown lists to. In our case, we select B2 via B10.
Then click on the Data.
In the Data Toolsclick on the data validation.
Add a dropdown list to selected cells
the data validation screens. About him SettingsYou can have Excel restrict entries in selected cells to dates, numbers, decimals, times, or a certain length.
For our example, select List from the Allow to create a dropdown list in each of the selected cells.
Select the font for the options from the dropdown list
Now, we need to specify the source of the options in each dropdown list. there are two ways to do this. The first method is to manually type the options into the Source separated by commas. This can be time consuming if you have a long list of items.
Earlier in this article, we created a list of items on a separate worksheet. Now we’re going to use that list to populate the dropdown in each of the selected cells. This second method is easy to handle. You can also hide the worksheet that contains the options (right-click the worksheet tab and select Hide) when you distribute the workbook to your users.
To add the separate worksheet list to your dropdown list, click the up arrow on the right side of the dropdown list. Source.
the data validation is reduced to just Sourceand you can access your workbook in the dialog. Click the worksheet tab that contains the dropdown options.
Next, select the cells that contain the options. The name of the worksheet and the range of cells with the options are added to the Source about him data validation.
Click the down arrow on the right side of the Source to accept the input and expand the dialog box.
Add an input message
You can add an optional message to the dropdown list. Perhaps you want to display a help message or tip. It’s a good idea to keep your message short.
To add a message that is displayed when a cell containing the dropdown list is selected, click the button InputMessage. Then enter a Title and the inputmessage in the boxes.
Add an error alert
Another option item in the dropdown is an error message, which appears when a user attempts to enter data that does not match the validation settings. In our example, the error message appears when someone types an option in the cell that doesn’t match any of the default options.
To add an error message, click the ErrorAlert. The default option for style of the error alert is Stop. You can also select Warning either Information. For this example, accept the default option of Stop in the style.
Enter the Title Y error message For him ErrorAlert. It is best to keep the error message short and informative. Click okay.
Use your new dropdown list
Now when you click on a cell you’ve added a data validation list to, a dropdown will appear and you can select an option.
If you added a InputMessage to the dropdown list, is displayed when you select a cell that contains the dropdown list.
If you try to enter an option that does not match any of the default options, the ErrorAlert configure visualizations in a dialog.
How have you made use of drop down lists in Excel? Let us know in the comments. Also, check out our other Microsoft Office tips and our tutorial for creating dropdown menus in Google Sheets.
