By creating a drop-down list in Excel, you can force users to choose a value from the list. For example, you can create a drop down list where users have to choose the division or unit to which they belong. If an individual attempts to enter a different value, Excel rejects the entry.
To create a drop-down list in Excel 2007:
- Create the list in cells A1:A4. Alternatively, you can enter the items in a single row, such as A1:D4.
- Select the cell where you want to position the list, such as C2.
- Choose Validation from the Data menu.
- Choose List from the Allow option’s drop-down list.
- Click the Source control and drag to highlight the cells A1:A4. Alternately, simply enter the reference (=$A$1:$A$4).
- Make sure the In-Cell Dropdown option is checked. If you uncheck this option, Excel still forces users to enter only list values (A1:A4), but it won’t present a drop-down list.
- Click OK.
[Photo above by Jennifer Chernoff / CC BY-ND 2.0]