Creating Drop-Down Lists in Excel

To make data entry easier, you can create a drop-down list of valid entries which are compiled from cells elsewhere in your Excel spreadsheet or workbook. When you create a drop-down list for a cell, an arrow is displayed in that cell with an input message if desired. To enter information in the cell simply click the arrow and then click the selected entry.

This Tech TIP illustrates how to create a drop-down list within a single worksheet that includes the data input area.

To create a drop-down list from a range of cells in Excel 2007, use the Data Validation command found in the Data Tools group on the Data tab.

1. To create a list of valid entries for the drop-down list, type the entries in s single column or row without blank cells. For example:

2. Select the cell where you want the drop-down list.
3. On the Data tab, in the Data Tools group, click Data Validation.
4. In the Data Validation dialog box, clikc the Settings tab.
5. In the Allow box, click List.
6. In the Source box, enter a cell reference (range of cells that contain the entries for the drop-down list).
7. Make sure that the In-cell dropdown check box is checked.
8. To specify whether the cell can be left blank, select or clear Ignore Blank check box.
9. Would you like to display an input message when the cell is clicked?

• Click the Input Message tab.
• Make sure that the Show input message when cell is selected check box is selected.
• Type the title and text for the message (up to 225 characters)

10. How do you want Excel to respond when invalid data is entered? The default message is "The value you entered is not valid. A user has restricted values that can be entered into this cell." If you wish to create a customized error message:

• Click the Error Alert tab, and make sure that the Show error alert after invalid data is entered check box is selected.
• Select one of the following options for the Style box:
• To display an information message that does not prevent entry of invalid data, click Information.
• To display a warning message that does not prevent entry of invalid data, click Warning.
• To prevent entry of invalid data, click Stop

• Type the title and text fot the message (up to 225 characters)