How-To

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

Drop-Down Lists Using Data Validation in Excel

The drop-down list feature in Microsoft Excel makes data entry easy, consistent and standardized. Let’s review how it works and how to enable it.

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.

Today I’ll show you how to create a drop-down list in a cell using Data Validation. Although I’m taking the screenshots using Excel 2016, the steps are the same if you’re using an older version 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, we’re going to enter a list of options for the drop-down list. You can do that when you define the data validation or you can define a list in another location on the same worksheet or on another worksheet.

For this example, we’re going to list the options for the drop-down list on another worksheet so click one of the worksheet tabs at the bottom of the Excel window.

01-Create-data-table-and-click-Sheet2

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

02-Create-list-of-options

Turn on Data Validation for Selected Cells

For this example, we want to add drop-down lists to the Rating column, or column B. Select the cells you want to add the drop-down lists too. In our case, we selected B2 through B10.

Then, click the Data tab.

03-Select-cells-and-click-Data-tab

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

04-Click-Data-Validation-in-Data-Tools

Add a Drop-Down List to the Selected Cells

The Data Validation dialog box displays. On the Settings tab, you can have Excel restrict entries in the selected cells to dates, numbers, decimals, times, or a certain length.

For our example, select List from the Allow drop-down list to create a drop-down list in each of the selected cells.

05-Select-List-in-Allow-on-Settings-tab

Select the Source for the Drop-Down List Options

Now, we 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.

Earlier in this article, we created a list of items on a separate worksheet. We’re now going to use that list to populate the drop-down list in each of the selected cells. This second method is easy to manage. You can also hide the worksheet containing the options (right-click on the worksheet tab and select Hide) when you distribute the workbook to your users.

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

06-Click-Source-Selection-button

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.

07-Click-Sheet2-for-Data-Validation-dialog

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.

08-Select-list-for-Data-Validation-dialog

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. Next, enter a Title and the Input message in the boxes.

09-Add-Input-Message

Add an Error Alert

Another option item on the drop-down list is an error message, which would display when a user tried to enter data that doesn’t match the validation settings. In our 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 this 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.

10-Add-Error-Alert

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.

11-Drop-down-list-in-cell

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

12-Input-Message-displayed

If you try to enter an option that doesn’t match any of the preset options, the Error Alert you set up displays on a dialog box.

13-Error-Alert-displayed

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 drop-downs in Google Sheets.


1 Comment

1 Comment

  1. So, what’s the verdict? Which pumpkin beers are superb?

Leave a Reply

Your email address will not be published. Required fields are marked *

 

To Top