Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, 2016, 2019, and Excel in Office 365. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Using Data Validation.
by Allen Wyatt
(last updated November 12, 2020)
One of the tools provided with Excel allows you to ensure that the data in your workbook is within specific bounds, and therefore of a higher quality than unchecked data. This tool is referred to as data validation and it allows you to set the bounds applicable to your data.
For example, let's assume you are creating a simple worksheet to calculate the future value of a sum, and that in order to do so, you need input from the user (regular deposit, interest rate, and months). You may want to place limits on the information which can be used in the worksheet. For example, you may want to make sure the user always enters an interest rate between 2.5% and 25%. Follow these steps:
Figure 1. The Data Validation dialog box.
Effectively, you have set a validation rule that says "accept any decimal value that is between 2.5% and 25%." If the user tries to add information to the cell that is outside the range you specified, they are notified that they have made an error and a different value must be entered.
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (11494) 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: Using Data Validation.
Program Successfully in Excel! John Walkenbach's name is synonymous with excellence in deciphering complex technical topics. With this comprehensive guide, "Mr. Spreadsheet" shows how to maximize your Excel experience using professional spreadsheet application development tips from his own personal bookshelf. Check out Excel 2013 Power Programming with VBA today!
When entering data in a worksheet, you may want to exercise some degree of control on the values that can be entered. ...Discover More
When setting up Excel for data entry, you often have to be concerned with what values are acceptable. For example, if ...Discover More
Drop-down lists are handy in an Excel worksheet, and you they can be even more handy if a selection in one drop-down ...Discover More
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
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.