How to Use Data Validation to Keep Excel Data Clean

By Stephen L. Nelson, E. C. Nelson

One useful command related to this business of keeping your Excel data clean is the Data Validation command. Use this command to describe what information can be entered into a cell. The command also enables you to supply messages that give data input information and error messages that attempt to help someone correct data entry errors.

To use Data Validation, follow these steps:

1Select the worksheet range where the to-be-validated data will go.

You can do this by dragging your mouse or by using the navigation keys.

2Choose the Data tab’s Data Validation command to tell Excel that you want to set up data validation for the selected range.

Excel displays the Data Validation dialog box.

3On the Settings tab of the Data Validation dialog box, use the Validation Criteria text boxes to describe what is valid data.

Use choices from the Allow drop-down list box, for example, to supply what types of information can go into the range: whole numbers, decimal numbers, values from the list, valid dates, valid times, text of a particular length, and so on.

Use choices from the Data drop-down list box to further define your validation criteria. The Data drop-down list box provides several comparisons that can be made as part of the validation: between, not between, equal to, not equal to, greater than, and so on.

Refine the validation criteria, if necessary, using any of the other drop-down list boxes available. Note: The other validation criteria options depend on what you enter into the Allow and Data drop-down list boxes. If you indicate that you want to allow only whole numbers between a particular range of minimum and maximum values, Excel provides Minimum and Maximum text boxes for you to enter or define the range.

However, if you select other entries from the Allow or Data drop-down list boxes, you see other text boxes appearing on the Settings tab. In other words, Excel customizes the Settings tab depending on the kind of validation criteria that you define.

4Fine-tune the validation.

After you describe the validation criteria, either select or deselect (clear) the Ignore Blank check box to indicate whether blank cells are allowed.

5Consider expanding the scope of the data validation.

Select the Apply These Changes to All Other Cells with the Same Settings check box to indicate whether the validation criteria should be expanded to other similar cells.

Click the Clear All button, and Excel clears (removes) the validation criteria.

6Provide an input message from the Input Message tab of the Data Validation dialog box.

The Input Message tab enables you to tell Excel to display a small message when a cell with specified data validation is selected. To create the input message, you enter a title for the message into the Title text box and message text into the Input Message text box. Make sure that the Show Input Message When Cell Is Selected check box is selected.

7Provide an error message from the Error Alert tab of the Data Validation dialog box.

You can also supply an error message that Excel displays when someone attempts to enter invalid data.

To create an error message, first verify that the Show Error Alert After Invalid Data Is Entered check box is selected. Then use the Style drop-down list box to select what Excel should do when it encounters invalid data: Stop the data entry on the user without the incorrect data entry, or simply display an informational message after the data has been entered.

8Just like creating an input message, enter the error message title into the Title text box. Then enter the full text of the error message into the Error Message text box.

Curious about the options in the Style drop-down list box? The style of the error alert determines what command buttons the error message presents when someone attempts to enter bad data. If the error style is Stop, the error message box displays Retry and Cancel command buttons.

If the error style is Warning, the error message box displays Yes, No, and Cancel command buttons. If the error style is Informational, the error message box displays OK and Cancel command buttons.