How to add a Drop Down List to a data cell in MS Excel

Force a selection from a dropdown list with preset options in an Excel worksheet cell.

Added on 28 Jun 2008

General Solutions:

Solution Summary:

For this example, we will create the option list in a separate worksheet, give the range of options (cells) a name, and apply list validation to another cell which will pull the options from the list.

Solution Details:

Our goal is to create the following dropdownlist in an Excel Worksheet.

First create a new Workbook, and if you so choose, create titles for the columns in the first row.
Next, create (or open) another blank worksheet, and enter the options that you want to have appear in the dropdown list in a single column.
Select the items added in step two (in the following screenshot, it would be A1 to A4), click on the formulas tab, and in the Defined Names section, click on Define Name.

In the New Name dialog box, enter a Name and click the OK button.
Switch back to the first worksheet, and select the cell(s) for which you want to apply the option list.
Select the Data tab and, in the Data Tools section, click Data Validation.

The Settings tab of the Data Validation dialog box is selected. Choose "List" from the Allow dropdownlist. In the Source textbox, enter an equal sign followed by the name specified in step 3 above (e.g., "=Types").
Click the OK button. Now you should have a dropdownlist in the selected cell(s).

Was this solution useful? Yes No

Added on 28 Jun 2008

Rating: