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.

Data Validation Dialog

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

Data Validation Alert Styles

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.

Data Validation Error Alert Style Stop

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.

Data Validation Error Alert Style Warning

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.

Data Validation Error Alert Style Warning

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.

Data Validation Circle Invalid Data

 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!

 

 

Posted in ,

Jeff Lenning

I love sharing the things I've learned about Excel, and I built Excel University to help me do that. My motto is: Learn Excel. Work Faster.

Excel is not what it used to be.

You need the Excel Proficiency Roadmap now. Includes 6 steps for a successful journey, 3 things to avoid, and weekly Excel tips.

Want to learn Excel?

Access all Undergrad and Masters lessons with a Campus Pass or CPE Pass. Includes on-demand training plus live office hours.

5 Comments

  1. Denise Weber on April 24, 2013 at 1:55 am

    Hmmm, no cheat helps for the extra credit here…Darn! …smile…

  2. MariaLuisa Badani on August 4, 2022 at 4:48 pm

    Love all these options…. we can get really creative… lol

  3. Darrin C. on August 17, 2022 at 8:12 pm

    Been using Excel for a long time and never even knew of this feature. Cool stuff!

  4. Lena BAsh on February 9, 2023 at 10:53 am

    I like the idea of Excel circling entries that don’t conform. That saves time in trying to hunt down differences.

  5. LorraineMarie on October 31, 2023 at 8:15 am

    New to Excel so I am open to all the tips I can get!

Leave a Comment