Excel tips

Adding a Selection List

A selection list lets users choose an entry instead of typing one in. This is a good way to ensure that accurate data is entered, and it also saves typing. You can also team the list entry with a lookup table so that, depending on the entry selected, other information is entered into a nearby cell. For example, if you offer different shipping options, you can create a lookup table with the rates for each. Then, when the user selects an option from the list, the corresponding shipping amount will be automatically retrieved from the lookup table (Figure 1).

To create a selection list, first create the entries for the list. These can be on the same worksheet, in another worksheet, or in another workbook. If the list is in another worksheet or workbook, you must create a name for the list using Insert | Name | Define. Type a name for the list and make sure the Refers to: area includes a reference to the sheet and cells or to the workbook, sheet, and cells that contain the list. If you're using a list in another workbook, it must be open when you're entering data or the list won't appear.

Now select the range of cells into which users will enter list data and choose Data | Validation and the Settings tab. From the Allow drop-down list, choose List and type the range containing the list in the source box. If the list is in another worksheet or workbook, type the named range prefixed with an equal sign (=), so that Excel will treat it as a named range and not as a list item. Check the In-cell dropdown box and, when you're done, click on OK to finish. Test your list. You'll see that when a cell with this rule attached is selected, the drop-down list appears, letting the user choose an option from it.