Data Validation Error Messages in Excel

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 users know when they type something that does not match the rule you set.

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

You can set these in the Data Validation dialogue box, in the “Error Alert” tab.

Types of Data Validation Errors

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

1. Stop: 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.

Excel Data Validation Default Stop Error Alert

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.

Excel Data Validation Default Warning Error Alert

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.

Excel Data Validation Default Information Error Alert

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

Customize Your Data Validation Error Message

You might want to let your users know why the error happened so that they know how to correct it.

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 and your user types only 1 character.

You can set a custom error message to let them know to type exactly 2 characters.

You can create a custom error message from the Error tab in the Data Validation dialogue box in just 4 steps:

1. Check the box that reads “Show error alert after invalid data is entered.” If this is unchecked, no error message will display and users will be able to enter invalid data.

Excel Data Validation Show Error Alert Checkbox

2. Select which of the 3 types of Error Alerts (see above) you’d like for your project.

3. Create a Title for your Error alert. In the example of limiting the number of characters entered in a cell, you could use a title like “Character Limit Surpassed.” The Error title should be short and to the point.

Excel Data Validation Custom Error Alert Title Field Box

4. Create a custom Error Message to help your users. You can use the Error Message field to provide more detail on the error and instructions for how to fix it. Keep in mind that it’s still a good idea to be brief.

In the example of setting character limits, you could use an Error Message like “The entry for this cell needs to be 2 characters in length.”