Create A Drop-Down List Of Possible Input Values
If you're creating a worksheet that will require user input and you want to minimize data entry errors, use Excel's data validation feature to add a drop-down list. The best part about it is that you don't have to write any macros.
Data validation is an excellent way to ensure that a cell entry is of the proper data type (text, number, or date) and within the proper numeric range. The drop-down list produced with the feature appears when a user clicks the cell.
Here's how to create a drop-down list:
- Type the list of valid entries in a single column. If you like, you can hide this column (select Format, Column, Hide).
- Select the cell or cells that will display the list of entries.
- Choose Data, Validation, and select the Settings tab.
- From the Allow drop-down list, select List.
- In the Source box, enter a range address or a reference to the items that you entered in step 1.
- Make sure the 'In-cell dropdown' box is selected.
- Click OK.
If your list is short, you can skip step 1 and type the list entries directly in the Source box in step 5, separating items with a comma.
The Data Validation dialog box has two other tabs. Click Input Message to add a prompt that will appear when a user selects a cell. Click Error Alert to specify a custom error message if the user's entry is invalid.
The handy data validation feature suffers from one serious flaw. If you paste an entry into a cell that uses data validation, the validation isn't performed. And if you select that cell again, the drop-down list no longer appears. Fortunately, you can circumvent this problem by protecting the worksheet: Select Tools, Protection, Protect Sheet.
Search for Tips
Browse Tips by Category
Needs tips? Here are two books, with nothing but tips:
Contains more than 200 useful tips and tricks for Excel 2007 | Other Excel 2007 books | Amazon link: John Walkenbach's Favorite Excel 2007 Tips & Tricks