How to

Use Drop-down lists in Excel

A mini-PD on how to use a drop-down list to enforce validation in Excel - useful for ITA U4O1.

1.  Type the allowable values (e.g. "Male" and "Female") into a row or column of the spreadsheet.  Putting it on a different sheet to your main data is a neat idea to keep it out of the way.

2.  Select the cells into which you just entered allowable values.

3.  In the name box (just under the font name box), enter a descriptive name for the allowable values (i.e. name the range), e.g. genders.  Make sure you hit ENTER to make the name stick.

4. Now, select the cells into which you want to force users to select from your list of allowable values.

5.  Go to menu Data > Validation.

6.  In the "Allow" box, set it to "List"

7.  In the "Source" box, type an equals sign and the name of the range you created earlier (e.g. =genders)

8.  That's it.  Now, when a user clicks one of the cells you selected in step 4, they have to choose an entry from your limited list.

You can remove the list restriction by repeating step 6 and changing its entry to "Any value".

Have fun!