Written by Allen Wyatt (last updated November 2, 2024)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, 2021, and Excel in Microsoft 365
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:
Figure 1. The Settings tab of the Data Validation dialog box.
Figure 2. The Error Alert tab of the Data Validation dialog box.
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, 2021, and Excel in Microsoft 365. You can find a version of this tip for the older menu interface of Excel here: Limiting Choices in a Cell.
Comprehensive VBA Guide Visual Basic for Applications (VBA) is the language used for writing macros in all Office programs. This complete guide shows both professionals and novices how to master VBA in order to customize the entire Office suite for their needs. Check out Mastering VBA for Office 2010 today!
When using data validation, you may want to reference a list of validation criteria contained on a different worksheet. ...
Discover MoreWant to establish a "bottom limit" on what dates can be entered in a cell? This tip presents two different ways you can ...
Discover MoreData validation allows you to create drop-down lists that aid data entry. If you want the indicator for the drop-down ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
There are currently no comments for this tip. (Be the first to leave your comment—just use the simple form above!)
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2025 Sharon Parq Associates, Inc.
Comments