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. Display the Data tab of the ribbon.
3. In the Data Tools group click the Data Validation tool. Excel displays the Data Validation dialog box.
4. The Settings tab should be displayed. (See Figure 1.)



Figure 1. The Settings tab of the Data Validation dialog box.

5. Using the Allow drop-down list, choose List.
6. In the Source box, enter your department names, separated by commas.
7. Display the Error Alert tab. On this tab you specify an error message that the user will see if they enter an improper department name. (See Figure 2.)



Figure 2. The Error Alert tab of the Data Validation dialog box.

8. In the Title field, enter the phrase "Enter Valid Department Name".
9. In the Error message box, enter a message that indicates what the user did incorrectly. You should also indicate the acceptable department names.
10. 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.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (8033) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, and Excel in Office 365. You can find a version of this tip for the older menu interface of Excel here: Limiting Choices in a Cell.