Data Validation Dialog

The Data Validation dialog allows you to create data validation rules that prevent users from entering invalid data in specific cells. Additionally, you can also set warning/ message alerts on entering invalid data.

The Data Validation dialog can be accessed by clicking the Data Validation button in the Data Tools group under the Data tab and selecting the Data Validation... option from the dropdown.

The following image displays how to access the dialog.

data_validation_dialog

The Data Validation dialog includes the following tabs:

Settings
Input Message
Error Alert

Settings

This tab allows you to create a validation rule by defining the validation criteria as per your requirement. Depending upon the criteria selected in Allow: dropdown list, the data validation rules are applied on the cell range.

The data validation criteria's available in Allow: dropdown are as follows.

Any Value

Using this validation criteria allows the cell to accept any input because there are no restricting rules defined.

List

Restricts data entry to predefined items in a list.

You can use the below options to add values to a list.

In the Source: option, you can type a list of comma-separated values, define a name for the cell range of valid cells, or select this cell range in the worksheet by using the Collapse Dialog button.
Additionally, you can check the In-cell dropdown checkbox to make a dropdown list of available values appear when selecting a cell to which the validation is applied.

The image below illustrates the dialog with List option selected.

list dialog

Whole Number

This option allows you to set rules for entering whole numbers within a specific range (minimum and maximum).

The image below displays the dialog with Whole number option selected.

whole number dialog

Decimal

This option allows you to set rules for entering decimal numbers within a specific range (minimum and maximum).

The image below displays the dialog with Decimal option selected.

decimal_dialog

Date

This option restricts the dates outside a certain time frame. You can specify the start and end dates. So, only dates that satisfy the criteria will be displayed.

The image below displays the dialog with Date option selected in the Allow: dropdown list.

date_dialog

Time

This option restricts the times outside a certain time frame. To do this, use Start time: and End time: option.

The image below displays the dialog with Time option selected in Allow: list.

Time_dialog

Text Length

You can limit the number of text characters allowed in a cell by defining its text length. You can do this by specifying minimum and maximum length of characters.

The image below displays the dialog with allowed Text Length.

text_ length dialog

Custom

This option validates data based on formulas or values in other cells. The formula should return a logical value: TRUE for valid values and FALSE for incorrect values. The formula may also contain cell references, so that you can calculate the allowed values based on the content of other cells.

The image below displays the Custom rule applied to a cell range.

Custom_dialog

Note: To avoid getting error messages in blank cells, check the Ignore blank option. This will help you to leave the cell blank if the validation rule is not required in the particular cell.

Input Message

Options under this tab allow you to create a message along with a title that explains to the user what kind of data is allowed in a cell. For the input message to be displayed properly, make sure the Show input message when cell is selected box is checked.

The image below displays the title and input message in data validation dialog window.

Data_validation_input_message

Error Alert

This tab allows you to create an error alert that appears when incorrect data is entered.

Make sure the Show error alert after invalid data is entered option is checked.
Note: If this is unchecked, no error message will display, and users can enter invalid data.
Select the required type of error alert from the dropdown.
Enter a short and meaningful title for your error alert.
Create a custom error message to provide more information about how to fix the error.

The image below displays the error alert set for selected cell.

error_alert

The table below describes different alert type messages along with their descriptions.

Alert Type	Description	Images
Stop (default)	This alert type prevents user from entering invalid data. You can’t move forward until you enter the correct value or click Cancel to remove the entry.
Warning	This alert type warns users that the data entered is invalid but does not prevent entering it.
Information	This is a permissive alert type that only informs users about an invalid data entry.