Preventing Data Entry Errors with Data Validation
Excel’s Data Validation feature makes it easy to prevent users from entering the wrong type of data or invalid values in a particular cell range of the spreadsheet. You can also limit data entry errors by using Data Validation to compel the user to select the entry for a cell from only those values you display on the cell’s drop-down list.
To restrict data entry for the selected cell, you select the type of entry that is allowed in the cell on the Allow drop-down list (Whole Number, Decimal, List, Date, Time, Text Length, or Custom) on the Settings tab of the Data Validation dialog box (Data => Data Validation or Alt+AVV). Depending upon the type of entry you allow, you then restrict the range of allowable values. For example, if you select Date as the allowed entry type, you then specify the start and end dates that the cell entry must be between.
By selecting List as the entry type, you are able to create a cell drop-down list that contains the complete list of allowed entries. That way, the spreadsheet user does not have to type anything in order to make the cell entry. This type of cell data validation is perfect when its data entry is restricted to a relatively short list whose values must be consistent and spelled correctly (as when sorting or filtering data).
Note, however, that you must enter the list of allowed entries you want to appear on the cell’s drop-down list in a cell range somewhere in a worksheet prior to setting up this kind of List entry in the Data Validation dialog box. You then specify the allowable entries for the cell drop-down list by selecting the Source text box on the Settings tab and then selecting the cell range containing the allowed entries in the worksheet.
When setting up Data Validation for a cell, you can also define an input message displayed when the user selects the cell and an error alert message that is displayed when the user attempts to make an invalid data entry. The input message instructs the user on what range of values are allowed in the cell just as the error alert message informs the user why the entry he tried to complete was not allowed.
After setting up Data Validation for a single blank cell, you can then copy the validation settings to an entire range of cells in the spreadsheet using any of the standard copy methods (copy and paste or drag-and-drop).