Restrict Input Using Validation Rules
Data validation rules can help you or your colleagues enter data correctly by avoiding or reducing the possibilities of data entry errors. Excel can restrict data entry to certain cells by using data validation, which defines restrictions on what data can, or should be, entered in a cell, and creates a message that prompts users for correct entries and notifies users about incorrect entries
Restrict data entry
1. Select the cells that you want to restrict data entry in
2. From the Ribbon, click Data tab select Tools, click the down arrow next to Validate and choose Data Validation
NOTE If the validation command is unavailable, the sheet might be protected or the workbook may be shared. You cannot change data validation settings if your workbook is shared or your sheet is protected
3. Use tools in the Data Validation dialog box to create a data validation rule
- Select Settings tab
- Click Allow list button to specify the type of data that you want to allow
- Click Data list button to set the criteria that you want
- Enter limiting value in the Minimum box
- Deselect Ignore block checkbox to prevent any blank values entered
NOTE The boxes where you enter limiting values will be labeled based on the data and limiting criteria that you have chosen. For example, if you choose Date as your data type, you will be able to enter limiting values in minimum and maximum value boxes labeled Start Date and End Date.
4. If the input entry does not match the criteria set in the data validation rule, a message box is displayed to indicate that the value to be entered must be meet the criteria
- Click Cancel to clear the incorrect entry
- Click Retry to re-enter the entry
Delete a data validation rule
1. Click the cells you wish to apply data validation rule to
2. From the Ribbon, click Data tab, click Tools, then click Validate list button and choose Data Validation
NOTE If the validation command is unavailable, the sheet might be protected or the workbook may be shared. You cannot change data validation settings if your workbook is shared or your sheet is protected
3. Use tools in the Data Validation dialog box to create a data validation rule
- Choose Settings Header
- Do one of the following:
- Click Allow list button and choose Any value to remove a data validation rule applied in a selected cells range
- Click Clear All button to remove all data validation rules on the worksheet
- Click OK button
Prompt users for validation
When users click in a cell that has data entry requirements, you can display a message that explains what data is valid
1. Select the cells where you want to prompt users for valid data entries
2. From the Ribbon, click Data tab, select Tools, click Validate list button and choose Data Validation
NOTE If the validation command is unavailable, the sheet might be protected or the workbook may be shared. You cannot change data validation settings if your workbook is shared or your sheet is protected
3. Use tools in the Data Validation dialog box to create a prompt message
- Choose Input Message tab
- Select the Show input message when cell is selected check box
- Enter a title for the message in the Title box
- Enter the display message in the Input message box
Display an error message when invalid data is entered
If you have data restrictions in place and a user enters invalid data into a cell, you can display a message explaining the error
1. Select the cells where you want to prompt users to enter valid data entries
2. From the Ribbon, click Data tab, select Tools, click Validate list button and choose Data Validation
NOTE If the validation command is unavailable, the sheet might be protected or the workbook may be shared. You cannot change data validation settings if your workbook is shared or your sheet is protected
3. Use tools in the Data Validation dialog box to create a display error message
- Choose Error Alert tab
- Select the Show input message when cell is selected check box
- Enter a title for the message in the Title box
- Enter the display message in the Input message box
- Click Style list button and choose of the following style options
Mark invalid data
You can apply a data validation rule to cells that already have data entered
1. Select the cells that already have data entered
2. From the Ribbon, click Data tab, select Tools, click Validate list button and choose Circle Invalid Data
NOTE If the validation command is unavailable, the sheet might be protected or the workbook may be shared. You cannot change data validation settings if your workbook is shared or your sheet is protected
3. Excel draws a red circle around any cell that contains invalid data
4. To remove the data validation circle, from the Ribbon, click Data tab, select Tools, click Validate list button and choose Clear Validation Circle
