How to Utilize Data Entry Rules to Validate data in Excel 2007
In Excel, Data Entry Rules can be used to restrict data that is entered to ensure that it is in the correct format. That data can be restricted to just whole numbers, decimals, dates, times or even a specific length. Also, you can specify whether the values need to be between, not between, equal to, not equal to, greater than, less than, greater than or equal to, or less than or equal to the values you specify. Data Entry rules can allow you to create an input message that appears when the user enters the cell, as well as an error alert that displays if the user makes an incorrect entry. These can stop the user, provide a warning, or just provide information. Once your Data Entry Rule has been created, you can copy and paste it using the Paste Special Validation option. This tip is for Excel 2007.
How to Validate Data using Data Entry Rules
- Select the cell where you want your data entry rule to go.
- From the Data tab, Select Data Validation in the Data Tools group. The Data Validation Dialog box should appear.
- Click the Settings tab.
- From here, you can select your validation criteria from both drop down boxes.
- Type the criteria or click and drag to select the cells with the criteria you want to use.
- Click the Input Message tab and enter both a title for your message and the input message.
- You can also click the Error Alert tab and select a style for your error message and modify several other parameters which are self-explanatory.
- Click Ok.
If you had selected cells to specify your validation criteria, you can change the criteria as needed without changing the validation rule. When you make an incorrect entry, the Stop Error Alert style will display the error message that was previously entered and will prevent you from making an invalid entry. On the other hand, The Warning Alert and Information Alert styles will allow you to enter the data nonetheless.
