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, lets 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 (2853) applies to Microsoft Excel 97, 2000, 2002, and 2003. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) here: Using Data Validation.
Save Time and Supercharge Excel! Automate virtually any routine task and save yourself hours, days, maybe even weeks. Then, learn how to make Excel do things you thought were simply impossible! Mastering advanced Excel macros has never been easier. Check out Excel 2010 VBA and Macros today!
When using data validation, you might want to have Excel display a message when someone starts to enter information into ...
Discover MoreIt is not unusual to use Excel to gather the answers to users' questions. If you want your users to answer your questions ...
Discover MoreWhen setting up a worksheet for others to use, you might want to make some limitations on what can be entered in certain ...
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 menu interface (Excel 97, Excel 2000, Excel 2002, or Excel 2003)? This site is for you! If you use a later version of Excel, visit our ExcelTips site focusing on the ribbon 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