November 28, 2011
We all have used list boxes when filling out information on forms. The first one that comes to mind is the State box. Click the arrow and the list of states drops down for you to select. Using a list box like that guarantees that the selection will always be spelled correctly and will not be missed if you decide to sort or filter your data. Creating a list box is easy, once you know how. (Figuring out how to do something in Microsoft Office can sometimes be a daunting task!)
To create a drop-down list from a range of cells, use the Data Validation command in the Data Tools group on the Data tab.
- To create a list of valid entries for the drop-down list, type the entries in a single column or row without blank cells. You may want to sort the data in the order that you want it to appear in the drop-down list.
- If you want to use another worksheet, type the list on that worksheet, and then define a name for the list.
- Select the cell where you want the drop-down list.
- On the Data tab, in the Data Tools group, click Data Validation.
- In the Data Validation dialog box, click the Settings tab.
- In the Allow box, click List.
- To specify the location of the list of valid entries, do one of the following:
- If the list is in the current worksheet, enter a reference to your list in the Source box or click in the Source box and then select your list in the current spreadsheet to populate the reference automatically.
- If the list is on a different worksheet, enter the name that you defined for your list in the Source box.
In both cases, make sure that the reference or name is preceded with an equal sign (=). For example, enter =ValidDepts.
- Make sure that the In-cell dropdown check box is selected.
- To specify whether the cell can be left blank, select or clear the Ignore blank check box.
- Optionally, display an input message when the cell is clicked.
- Specify how you want Excel to respond when invalid data is entered, by doing the following:
- 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 for the message (up to 225 characters).
Note: If you don't enter a title or text, the title defaults to "Microsoft Excel" and the message to: "The value you entered is not valid. A user has restricted values that can be entered into this cell."
To delete a drop down list, select the cell with the list. Click the Data tab, and then in the Data Group, click Data Validation. In the Data Validation dialog box, click the Settings tab, and then click Clear All.