Data Validation Error Alert Messages in Excel (with video tutorial)

We may be compensated for your purchase of any of the products featured on this page – it helps us keep the lights on :)

Data Validation rules in Excel are used to control what users can enter into a cell. When you create a Data Validation rule, you can use Data Validation Error Messages to let your users know when they’ve type something that does not match the rule you set.

You can even create custom error messages to give your users better information and directions.

WATCH: Data Validation Error Alerts in Excel – Video Tutorial

Create a Data Validation Rule

The first thing you’ll need to do before you begin working on your error message is, of course, to create a Data Validation rule.

Here is a quick overview of how to create a Data Validation rule:

1. To create a Data Validation rule, you’ll first need to select the cells you want to apply the rule to
2. Then, go to the Data tab of your Excel Ribbon. Data Validation will be in the Data Tools section
3. Once you click on Data Validation, the Data Validation pop-up window will appear
4. You’ll create your rule in the “Settings” tab of the pop-up window
5. Under “Criteria,” select the type of data you want to allow (whole numbers, text length, dates, time, items from a list, etc)
6. Set the parameters for your rule (whole numbers between certain values, the number of characters in a text length, a certain date, etc)
7. Click OK

What are Excel Data Validation Error Alerts???

As the name implies, Data Validation rules validate the data entered into any cell that has a rule applied to it.

Once you’ve created a Data Validation rule, any entry into a cell containing the rule that does not match the rule settings will be considered an “error.”

There are 3 different types of Error Alert styles to choose from. They range in flexibility from not allowing an error to allowing the entry but notifying the user that there’s an error.

The “Error Alert” tab of the Data Validation window is where you’ll find options for setting these permissions.

You can also create custom error alert messages here.

Data Validation Error Alert Styles

There are 3 types of Data Validation Error Alert Styles to choose from and each behaves a bit differently:

1. Stop: This is the default error alert style. A Stop error alert prevents users from typing anything that does not match the settings in your data validation rule.

For example, if you create a data validation rule that says only 2 characters can be entered in a cell, and use a Stop error alert, users would see this error message and would not be able to proceed unless their entry was exactly 2 characters.

2. Warning: A Warning error alert communicates that an error has been entered in a given cell but gives the user the option to ignore the error and continue with their entry without making a correction.

3. Information: An Information error alert tells the user that their entry does not match the data validation rule for the cell but allows them to continue without fixing it.

These error alerts will display the default message like in the images above, but you can customize an error message for your users.

Create a Custom Data Validation Error Message

You might want to let your users know why they got an error and share instructions so that they know how to correct it.

To do this, you can create a custom Data Validation error message that they will see when they type something that does not match the Data Validation rule you set.

Let’s say you created a Data Validation rule that limits the number of characters that can be entered in a given cell(s) to 2 characters. But your user typed only 1 character, so they got an error alert.

You can create a custom error message with instructions letting them know to type exactly 2 characters.

The custom Data Validation Error Message in this example would look like this:

---

---

To see how to create custom messages for formula errors in Excel, take a look at our tutorial on how to use the IFERROR function.