Drop-Down Lists

Using Data Validation it is possible to display a drop-drop list inside a cell to give the user a set of choices.
The method outlined below shows you how to do this when the list of choices is entered on the same worksheet.
It is possible to have the list on a separate worksheet although this requires the use of a named range.

Adding a Drop-Down List Box

When cell "D2" is selected we want a drop-down box of possible options to be displayed automatically.
These are the only values that are allowed in that particular cell.
This can be either be typed in a single row or a single column block
Let us enter our list as a single column block in cells (B2:B6).

We want to place the drop-down list in to cell "D2" so this must be selected before displaying the dialog box.
One of the restrictions of the Data Validation dialog box is that you can only use a cell reference on the same worksheet as the worksheet you want the drop-down list displayed.
There are workarounds for this this though which are described towards the bottom of this page.

Enter the Criteria

Press (Data > Validation) to display the Data Validation dialog box and select the Settings tab.
In the "Allow" drop-down box select "List" and in the "Source" box enter the range of cells containing your list.
You can either use the mouse to select the cells or you can type the necessary range reference.
Using the mouse will guarantee the correct range reference is entered.

Cell ranges do not have to be absolute ??
Ignore blank - Any blank cells included in the range will not appear in the drop-down list. Including some blank cells at the bottom of your list will allow you to add to the list at a later date without redefining the source range.
In-cell drop-down - Make sure this is checked otherwise a drop-down list box will not be displayed when the cell is selected.
Press OK to apply the data validation to cell "D2".

Making a Selection

Directly in the Data Validation Dialog Box

List items do not have to be entered onto the worksheet but can actually be entered directly into the Data Validation dialog box.
If you do not want to refer to a worksheet at all and the list of choices is unlikely to change, you can use a comma separated list:

Important

However you enter (or type) the items they are always case sensitive.
It is possible to allow users to enter items that are not in the list by clearing the checkbox at the top of the Error Alert tab.
This is often done on a part of the worksheet that can either be hidden from view or will not be found (i.e. column IV).
If your list contains more than a couple of items it will probably be easier to maintain the list when it is types directly onto the active sheet.