How to Create Drop-Down Lists Using Data Validation in Microsoft Excel

Printer Friendly
Text Size: A A A A

Data Validation allows you to do things that involve complicated custom settings like prompting users to choose an option from a list or restricting cells to certain data types. It’s extremely useful when you need to keep your Microsoft Excel data consistent and standardized.

These instructions work in Excel 2016, as well as older versions.

Create a Data Table and the List of Options


First set up a basic data table. You can either type your data in manually or copy and paste it from another source.

Next enter a list of options for the drop-down list. You can do this when you define the data validation, or you can define a list in another location (either on the same worksheet or on another worksheet).

If you want to list the options for the drop-down list on another worksheet, click one of the worksheet tabs at the bottom of the Excel window.

Enter each option in a column (or row), one option to a cell. Then go back to the worksheet that contains your data.

Turn on Data Validation for Selected Cells


Now you will add drop-down lists to the Rating column, or column B. Select the cells to which you want to add the drop-down lists. In this case select B2 through B10.

Click the Data tab.

In the Data Tools section, click the Data Validation button.

Add a Drop-Down List to the Selected Cells


Within the Data Validation dialog box, open the Settings tab. You can force Excel to restrict entries in the selected cells to dates, numbers, decimals, times, or a certain length.

You might select List from the Allow field to create a drop-down list in each of the selected cells.

Select the Source for the Drop-Down List Options


You need to specify the source for the options in each drop-down list. There are two ways you can do this.

The first method involves manually typing the options in the Source box separated by commas. This can be time consuming if you have a long list of items.

The second method is faster because you've already created a list of items on a separate worksheet. You're going to use this list to populate the drop-down in each of the selected cells. You can also hide the worksheet containing the options (right-click on the worksheet tab and select Hide).

To add the options to your drop-down list from a separate worksheet, click the up arrow on the right side of the Source box.

The Data Validation dialog will shrink down to just the Source box. You can access your workbook underneath the dialog box. Click the tab for the worksheet containing the drop-down list options.

Select the cells that contain your options. The worksheet name and the cell range will be added to the Source field in the Data Validation dialog box.

Click the down arrow on the right side of the Source box to accept the input and expand the dialog box.

Add an Input Message


You can add an optional message to the drop-down list. Maybe you want to display a tip or help message. It’s a good idea to keep the message short.

To add a message that displays when a cell containing the drop-down list is selected, click the Input Message tab. Then enter a Title and Input Message.

Add an Error Alert


You could also add an error message, which would display when a user tries to enter data that doesn’t match the validation settings. The error message would appear if someone manually typed an option (that doesn’t match any of the presets) into the cell.

To add an error message click the Error Alert tab. The default option for the Style of the alert is Stop. You can also select Warning or Information.

Enter the Title and Error Message for the Error Alert. It’s best to keep the error message short and informational. Click OK.

Use Your New Drop-Down List


Now when you click on a cell that contains your data validation list, a drop-down will display and you can select an option.

If you added an Input Message, it will display when you select a cell containing the drop-down list.

If you try to enter an option that doesn’t match any of the presets, you will see an Error Alert.

For more information and to view an archive of previous Tech Tips, please visit us here.

Do you have specific topics you would like to see covered in Tech Tips? Email any suggestions to