Which data validation alert style should you use to prevent the user from entering invalid data?

Show Data Validation Messages

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

Data Validation Messages

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:

1. An Input Message can be displayed when a cell is selected.
   

2. An Error Alert can be displayed if invalid data is entered in a cell

Video: Create an Input or Error Message

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.

Create an Input Message

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.

• Select the cells in which you want to apply data validation
• On the Ribbon, click the Data tab
• In the Data Tools group, click Data Validation
• (optional) On the Settings tab, choose the data validation settings
• Click on the Input Message tab, and add a check mark to Show input message when cell is selected

• Type your message heading text in the Title box. This text will appear in bold print at the top of the message.
• Type a short message in the Input message box. Press the Enter key, to create line breaks, if you want them.
  • NOTE: The message text length limit is 255 characters

• Click OK or follow the steps below to add an Error Alert.
• Now, when you click on the cell, the Input Message will appear.

Input Message Size

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.

Input Message Position

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.

Move an Input Message

When an input message appears, you can temporarily drag it to a different location on the worksheet.

• The location is only temporary -- the message box will return to its original position, when you close and reopen the workbook.
• ALL input messages on that worksheet will appear in that location, until the workbook is closed and reopened.

Data Validation Error Alert

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:

• Title: Microsoft Excel
• Icon: Red circle with white X
• Text: This value doesn't match the data validation restrictions defined for this cell
• Buttons: Retry, Cancel, Help, Close (X at top right)

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.

Retry

• Click Retry button, or press Enter key on keyboard
• Message box closes
• Data validation cell contents are selected, ready for you to type a different value in the cell

Cancel

• Click Cancel button, or press Esc key on keyboard
• Message box closes
• Data validation cell contents are removed

Help

• Click Help button, or type Alt + H on keyboard
• Message box stays open
• Web browser open Data Validation page on Microsoft website

Close (X)

• Click X at top right of message box, or press Esc key on keyboard
• Message box closes
• Data validation cell contents are removed

Change Error Alert Settings

Instead of leaving the default data validation error alert settings, you can make changes to them. For example:

• Turn Error Alert off, to allow people to enter invalid data
• Leave Error Alert on, but do not automatically stop invalid entries
• Create a custom error message, to explain why the data is invalid, and how to correct it

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.

• Select the cells in which you want to apply data validation
• On the Ribbon, click the Data tab, and click Data Validation
• In the Data Validation dialog box, go to the Error Alert tab

At the top of the Error Alert tab, there is a check box for the setting:

• Show error alert after invalid data is entered

Choose Error Alert Style

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:

• Click on the Style box drop-down arrow
• Click on the Style you want to use - Stop, Warning, or Information

Error Alert Style Features

Here are the features and behaviours for the 3 types of Error Alert styles

Stop - Prevents the entry of invalid data

• Error message icon is red circle with white X
• If Retry button is clicked, the invalid entry is highlighted, and can be overtyped.
• If Cancel button is clicked, the invalid entry is deleted, and the cell's original content is restored.
• Invalid entry cannot be left in the cell - only valid entries are allowed

Warning: Discourages the entry of invalid data.

• Error message icon is yellow triangle with black exclamation mark
• If Yes button is clicked, the invalid entry is accepted, and the next cell is selected.
• If No button is clicked, the invalid entry is highlighted, and can be overtyped.
• If Cancel button is clicked, the invalid entry is deleted, and the cell's original content is restored.
• You can choose to leave the invalid entry in the cell.

Information: Announces the entry of invalid data.

• Error message icon is white speech buttle, with blue lower-case "i"
• If the OK button is clicked, the invalid entry is accepted, and the next cell is selected.
• If the Cancel button is clicked, the invalid entry is deleted, and the cell's original content is restored.
• The user can choose to leave the invalid entry in the cell.

Create Custom Error Message

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.

Create a Custom Error Message

To create a custom error message, follow these steps:

• Type a short heading for the error message, in the Title box.
  • This text will appear in bold print at the top of the custom error message.
• Type a short message in the Error message box.
  • Clearly explain what the data validation rules are for this cell
  • Message text limit is 225 characters
  • See error message size notes in next section
• To apply the Error Alert settings, lick OK

Error Message Size

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.

• Tip: For messages that are more than a few words, press the Enter key, to create line breaks.
  Otherwise, the message box could be too wide to read easily.

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.

Turn Error Alert Off

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:

1. Select the cells in which you want to turn off Error Alert
2. On the Ribbon, click the Data tab, and click Data Validation
3. Click on the Error Alert tab, and remove the check mark from Show error alert after invalid data is entered

Error Checking in Tables

If data validation cells are in a named Excel table, invalid data might be flagged by Excel's Error Checking Rules, even if you have followed the instructions above, to turn off Error Alert messages.

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:

• This setting will affect all workbooks that you open.
• Other types of invalid data in the table will also be ignored, not just data validation errors

To change the setting:

• Click the Error Message button when it appears, then click Error Checking Options

• In the Error Checking Rules section, remove the check mark for “Data entered in a table is invalid”
• Click OK

Get the Sample File

Get a zipped Excel file with the data validation message examples. The Excel workbook is in xlsx format, and does not contain any macros

Related Pages

Data Validation Basics

Dependent Drop Down Lists

Data Validation Tips

How do I remove invalid data in Excel?

How do I protect data validation in Excel?

Do you want to make sure employees enter data only in particular cells and not change or delete other data What is the first step?

What is the feature that warns users from entering invalid data quizlet?