Very useful tip from Microsoft on how to create a dropdown list of values and provide them as options for filling out a cell in Excel.
First create a list of valid dropdown values in a single column or row in Excel (no blanks).
Now select your list, right click and choose “Define Name”. Give it a name.
Now (according to MS):
- Select the cell(s) where you want the drop-down list.
- On the Data menu, click Validation, and then click the Settings tab.
- In the Allow box, click List.
- To specify the location of the list of valid entries, enter the name that you defined for your list in the Source box. Make sure that the name is preceded with an equal sign (=). For example, enter =ValidDepts.
- Make sure that the In-cell drop-down check box is selected.