Limiting Choices in a Cell

When you are developing worksheets that will be used by others, you may want to limit what your users can enter into a particular cell. For instance, you might have a cell where the user should enter their department. You would obviously want them to only enter one of the valid departments for your company.

To ensure that only certain departments can be entered in the cell, follow these steps:

1. Select the cell where the user will input the department name.
2. Choose Validation from the Data menu. Excel displays the Data Validation dialog box.
3. The Settings tab should be displayed. (Click here to see a related figure.)
4. Using the Allow drop-down list, choose List.
5. In the Source box, enter your department names, separated by commas.
6. Display the Error Alert tab. (Click here to see a related figure.) On this tab you specify an error message that the user will see if they enter an improper department name.
7. In the Title field, enter the phrase "Enter Valid Department Name".
8. In the Error message box, enter a message that indicates what the user did incorrectly. You should also indicate the acceptable department names.
9. Click OK.

That's it. Now, the user can only enter one of your valid department names. Better yet, when they select the cell they will see a drop-down arrow at the right of the cell where they can select from the department names you specified in step 5. If they enter one that is incorrect, they will see the error message and will need to change what they entered.