An often-asked question (to me anyway) is “I’m creating a form in Excel. How do I create a drop-down list?” There’s several ways to set up a drop-down list in Excel and this tutorial covers the Data Validation method.
Data validation allows you to create a “validation rule” on a cell (or range of cells) so that the data that is entered must conform to the rule. There are several types of rule. for example, you could set up a rule on A1 so that the cell can only contain a number between 20 and 30.
The Data Validation feature can be used to create an in-cell drop down list. The cell must contain one of the values defined in the list.
To set up Data Validation, select the cell that is to have Data validation applied, click on the Data tab, click the Data Validation button and click Data Validation
The dialog box that appears is used to define the Validation Rule.
From the Allow field, select List
In the Source field, either type the required values (as per the screenshot below) or specify a range that contains the values. The range can be on the same sheet or a different sheet. Note that in Excel 2007 and earlier, if the range containing the values is on a different sheet to the cell containing the Data Validation, you must define a name for the range and in the Source field type the name preceded by an = sign (e.g. =MembershipLevels)
Click OK
A drop down list appears in the cell. When the user clicks the drop-down arrow, a list of values is displayed.
A short video that shows how to create a Data Validation drop down list can be found here