Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, and 2016. 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 7, 2015)
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, and 2016. You can find a version of this tip for the older menu interface of Excel here: Using Data Validation.
Create Custom Apps with VBA! Discover how to extend the capabilities of Office 2013 (Word, Excel, PowerPoint, Outlook, and Access) with VBA programming, using it for writing macros, automating Office applications, and creating custom applications. Check out Mastering VBA for Office 2013 today!
Want to establish a "bottom limit" on what dates can be entered in a cell? This tip presents two different ways you can ...Discover More
When entering data in a worksheet, you may want to exercise some degree of control on the values that can be entered. ...Discover More
It is not unusual to use Excel to gather the answers to users' questions. If you want your users to answer your questions ...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.