If you want users in Excel to select values from a predefined list instead of entering them manually, use a drop-down list.
Translator’s Note: Let’s call the sheet on which the drop-down list will be created Sheet1, and the auxiliary sheet – Sheet2.
- Enter items for the drop down list on the sheet Sheet2.
- Highlight a cell B1 on the sheet Sheet1.
- On the Advanced tab Data (data) click Data Validation (Data validation).A dialog box will open Data Validation (Checking input values).
- From drop down list Allow (data type) select List (List).
- Click the close dialog box button in the box Source (Source) and select range A1: A3 on the sheet Sheet2.
- Press OK.
Result:
Tip: If you don’t want users to have access to items on a sheet Sheet2, you can hide it. To do this, right-click on the sheet tab and click Hide (Hide).
You can also enter list items directly into the field Source (Source) dialog box Data Validation (Validation of input values) without using a sheet Sheet2. This will give the same result.