How to create Microsoft Excel data validation custom messages, to help people enter worksheet data correctly. Show special error messages to help people fix invalid entries without frustration
Author: Debra Dalgleish
With the options available in data validation, you can display messages to give instructions to the people who use your spreadsheet.
There are two types of data validation messages:
To see the steps for creating an input message and an error message, watch this short video tutorial. The written instructions are below the video.
To help people know what data should be entered in a cell, you can set up an Input Message that is displayed when the cell is selected.
Follow these steps to show a short message when a cell is selected.
Although there are 255 characters allowed in the Input Message box, the box has a maximum height and width, and all the characters might not fit.
NOTE: The size of the message box cannot be changed -- it is automatically set by Excel.
For example, in the message box below, there are 254 "i" characters, with an "X" at the end.
However, in the message box below, there are 254 "W" characters, with an "X" at the end. Only 126 of the characters appear in full, and the remaining characters are cut off, or not visible.
In most cases, the input message pops below the cell, with the left edge of the message at the middle point of the cell's width.
If the cell is close to the right side of the Excel window, the right border of the input message will start at the Excel window border.
If there is not enough room below the cell, the input message appears at the right side of the cell, if there is enough room there.
If there is not enough room below the cell, or to the right, the input message appears at the left side of the cell.
If there is a comment in the cell, the input message appears below the cell, with the right edge of the message at the middle point of the cell's width. This can cause problems in column A, where there is no room at the left, and the data validation message is cut off.
When an input message appears, you can temporarily drag it to a different location on the worksheet.
When you add data validation to a cell, its Error Alert feature is automatically turned on.
The default error alert settings block people from entering invalid data in the data validation cell.
With those default settings, if you try to type an invalid entry in the cell, Excel shows its default data validation error message. The default error message has these features:
That default message prevents you from entering the invalid data, but doesn't help anyone understand why the data is invalid. See the sections below, for ways to change the error alert settings and message.
Error Message Buttons
Here's what happens when you click any of the command buttons, or the Close button, in the data validation error message.
Change Error Alert Settings
Instead of leaving the default data validation error alert settings, you can make changes to them. For example:
See how to change the error alert settins, by following the instructions below.
Show Error Alert Setting
The data validation setting, Show error alert after invalid data is entered, is turned on by default. Follow these steps to turn that setting on or off.
At the top of the Error Alert tab, there is a check box for the setting:
If the setting, Show error alert after invalid data is entered, is turned on, there are three error alert styles that you can use. To choose a style, follow these steps:
Here are the features and behaviours for the 3 types of Error Alert styles
Stop - Prevents the entry of invalid data
Warning: Discourages the entry of invalid data.
Information: Announces the entry of invalid data.
The default data validation error message does not explain why the data is invalid, or how to fix the problem.
To help people who might use the data validation cells, create a custom error message, with details on the data validation rule that are applied to the cell.
To create a custom error message, follow these steps:
There are 225 characters allowed in the Error Message box, but size of the message box cannot be changed -- it is automatically set by Excel.
For example, in the message box below, there are 225 characters, with a line break after every second group.
Here is the same message, with no line breaks. It is almost the full width of the Excel window.
You can turn Error Alert off, to allow people to enter invalid data. For example, if the data validation cell contains a dropdown list, turn off the Error Alert to allow users to type items that are not in the list.
To turn off Error Alert:
In the screen shot below, error alerts have been turned off, to allow multiple selections from a drop down list. However, there is a Error Checking alert, for a data validation error, because the cell is in a table.
You can manually respond to each message, and select Ignore Error.
Or, you could turn off all the data checking for tables, by following the steps below. Please note that:
Get a zipped Excel file with the data validation message examples. The Excel workbook is in xlsx format, and does not contain any macros
Last updated: March 29, 2022 2:42 PM