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

Printer Friendly
Text Size: A A A A

http://www.groovypost.com/

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 steps are the same in Excel 2016, as well as older versions, such as Excel 2013.

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 on the same worksheet (or on another worksheet).

For example, 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 with your data.

Turn on Data Validation for Selected Cells

 

Next 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.

Then 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.

For example, you can select List from the Allow drop-down 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. You 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 from a separate worksheet to your drop-down list, click the up arrow on the right side of the Source box.

The Data Validation dialog box shrinks down to just the Source box and you can access your workbook under the dialog box. Click the tab for the worksheet containing the drop-down list options.

Next select the cells containing the options. The worksheet name and the cell range with the options is added to the Source box on 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 help message or tip. 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 the Input message in the boxes.

Add an Error Alert

 

Another option on the drop-down list is an error message, which would display when a user tries to enter data that doesn’t match the validation settings. For example, when someone types an option into the cell that doesn’t match any of the preset options, the error message displays.

To add an error message click the Error Alert tab. The default option for the Style of the error alert is Stop. You can also select Warning or Information. For example, accept the default option of Stop in the Style drop-down list.

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 to which you added a data validation list, a drop-down list will display and you can select an option.

If you added an Input Message to the drop-down list, it displays 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, the Error Alert will display.


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 communications@ficpa.org.