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.
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.
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.
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.
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.”
When you’re done, click OK.
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.
Leave a Reply