Display a User Message in an Excel Data Validation List

There are four different types of user messages available in Excel’s Data Validation feature. Read on to learn how you can create custom user messages to provide special instructions or present warnings to your worksheet users.

The first type is an input message. An input message will appear when the cell it is associated with is selected. The most common use for input messages is to provide instructions to the user.

For an input message, select the cell or cells that will require data validation. Apply Data Validation to the selection (if you are not sure how, check out the first article in this series on using data validation). While you are still in the Data Validation dialog, click the Input Message tab. Select the Show Input Message When Cell Is Selected checkbox, type a title into the Title box, and enter the user message into the Input Message box. Click OK to apply these changes.

click to enlarge

click to enlarge

The other three types of user messages you can make using Data Validation are applied in the same manner. Again, you will select the cells your user to which your user message should pertain. Apply Data Validation, but don’t close that dialog just yet. Instead, click on the Error Alert tab. Select the checkbox for Show Error Alert after Invalid Data is Entered.

click to enlarge

Choose one of the three options in the Style dropdown. Select Stop to completely prevent incorrect data. Enter a title, which will appear on the title bar of the user message box, and enter an error message. If the user enters data that does not meet the data validation criteria, they will view the user message and have the choice to click Retry or Cancel. If the user selects Retry, the entry will become active and the user can type over the incorrect data. If the user selects Cancel, the cell will be cleared.

click to enlarge

Selecting the Warning user message will not disallow incorrect data from users. However, the user will be warned that this data does not match the data validation criteria. As with the Stop message, you will enter a title and error message. Users will be asked if they want to continue, and can then choose to click Yes, No or Cancel. If the user selects Yes, the invalid data will remain. If the user selects No, the data will be highlighted so the user can type over it. If the user clicks on Cancel, the cell will be cleared.

click to enlarge

The final option in the Error Alert section is an information message. Again, if you select this type of user message you will enter a title and error message. If the data is not valid, users will receive a message informing them of this fact. The options a user can select for this message are OK and Cancel. If the user selects OK, the invalid data will stay put. The Cancel button will clear the cell.

click to enlarge

One final note on Data Validation user messages: the Input Message option does not require data validation. It can be applied to any cell or range of cells.