Data Validation Alert Styles

The Data Validation feature allows you to control what a user can type into a cell. This post digs into the details of the data validation alert style options.

Data Validation Primer

First, a quick overview of the Data Validation feature. Data Validation has been in Excel for a long time, and it is one of my favorite features. It allows us to control what a user can type into a cell.

When we select a cell or range, and then activate Data Validation, we are presented with the Data Validation dialog box, shown below.

The dialog box has three tabs:

• Settings
• Input Message
• Error Alert

The Input Message tab allows you to create a custom message to be displayed when a user selects the cell.

The Settings tab allows you to define the rule for valid data. For example, allow a whole number between 1 and 100, or, a decimal greater than zero. If a user enters a valid value, as defined, Excel accepts the entry and stores the value. If a user enters an invalid value, Excel displays an error alert dialog.

What is displayed in the error alert dialog? It depends on the Error Alert settings.

Error Alert

The Error Alert tab allows you to define what happens when a user enters an invalid value.  You can customize the error alert dialog box Title and Error message.  You can also pick a Style.

You’ll notice there are three choices for the alert style:

• Stop
• Warning
• Information

Each alert style gets its own icon. But more importantly, each style defines the behavior of this feature, and specifically, what message is displayed and which buttons are available in the alert dialog.

Stop

If you set the error alert style to Stop, then you are asking Excel to prevent the user from typing in an invalid value. A value that does not meet the condition specified on the Settings tab will be rejected, and will not be stored in the cell. This is the default style. The error alert dialog that pops up when an invalid value is entered is displayed below.

The default message to the user is “The value you entered is not valid.  A user has restricted values that can be entered into this cell.”  You can see the user has the option to click Retry, Cancel, Help, or to close the dialog box with the standard “X” button in the upper right of the dialog. None of these choices cause Excel to store the invalid value.

Warning

The other two alert styles, Warning and Information, allow the user to override the data validation settings and enter invalid data. When the Warning style is selected, an invalid entry brings up the dialog box below.

The default message to the user is “The value you entered is not valid.  A user has restricted values that can be entered into this cell.  Continue?”  You can see that the user has the option to click Yes, which will instruct Excel to store the invalid value in the cell. The No button is selected by default, so if the user is working quickly and disregards the message and hits the Enter key on the keyboard, the No button is selected and the invalid value is not stored.

Information

When the Information style is selected, an invalid entry brings up the dialog below.

The default message to the user is similar, except, you’ll notice the user can click the OK button.  The OK button causes Excel to store the invalid value.  The OK button is the default button, so if the user is working quickly and disregards the message and hits the Enter key, the invalid value is stored to the cell.

Circle Invalid Data

If you allow a user to override the validation settings by changing the alert style to Warning or Information, and you want a quick way to determine if any of the cells in the worksheet contain invalid data, then you can use the Circle Invalid Data command button. Excel will place a red circle around any cell that has a stored value that violates the data validation setting of the cell. This is illustrated in the screenshot below.

 Conclusion

The Data Validation feature is flexible, and provides options that enable us to use it in a wide variety of situations. Thanks Microsoft!

And remember, Excel Rules!