Excel Data Validation Error Messages

Do you like to use error checking in Excel, so that problem cells are flagged, or do you turn that feature off? There are options for data validation error messages too – do you use those?

Data Validation Error Messages

If you add a drop down list on a worksheet, or any other type of data validation, you can choose which type of Error Alert messages should be shown – Stop, Warning or Information.

The default setting is Stop, and that prevents you from entering invalid data. An error message appears, with buttons for Retry, Cancel and Help.

“This value doesn’t match the data validation restrictions defined for this cell.”

errormessage04

Respond to the Error Message

If you see that error message, what happens if you click one of the buttons?

  • Help – Takes you to a data validation page on the Microsoft website, where there are instructions for setting up a data validation cell. It won’t give you any specific details on why the value entered wasn’t valid.
  • Cancel – Clears the cell, so you can type a new value, or select from the drop down list, if there is one.
  • Retry – Highlights the value that you typed in the cell, so you can type a new value. You will have to clear the cell if you want to use the drop down arrow.

Customized Error Messages

Instead of leaving the default settings for data validation errors, you can go to the Error Alert tab in the data validation dialog box, and customize them.

Choose one of the Styles, and enter a Title and Error message that will give people details on what went wrong.

errormessage05

In this example, I selected Warning style, and entered a customized message. When I enter an item that isn’t in the list, the customized message appears, with a Warning icon, and different buttons.

errormessage06

Turn Off Error Messages

In some cases, you might want to turn off the data validation error alerts completely, like I do when allowing multiple selections from a drop down list.

On the Error Alert tab, remove the check mark from “Show error alert after invalid data is entered”

errormessage07

Data Validation Error Messages in Tables

If you’re using data validation in a named Excel table, invalid data might be flagged by Excel’s Error Checking Rules, even if you turned off error alerts, or set the Style to Warning or Information.

In the screen shot below, there is a warning on cell C2, because two items are in the cell.

errorcheck03

If you copied cell C2 to a different part of the worksheet, outside of a table, the error warning would disappear – it only affects tables.

Turn Off Table Data Setting

If you know that there isn’t really a problem with the cell’s data, you can simply ignore the error warnings. Or, if there are just a few messages, you can set the error warning in each cell manually, to Ignore Error.

If you want to turn off all of the data validation warnings in tables, you can follow the steps below, to change one of the Excel options.

  • Click the arrow on the Error alert, and click Error Checking Options
  • In the Options window, in the Formulas category, scroll down to the Error Checking Rules section.
  • Remove the check mark for “Data entered in a table is invalid”.
  • Click OK.

Keep in mind though, that Excel will stop flagging ALL data errors in your tables, in ALL workbooks that you open. You can read the details on my website.

ribbon shortcuts

Video: Data Validation Messages

To see the steps for creating a data validation input message or error message, watch this short video tutorial.

_______________

Save

You may also like...

2 Responses

  1. JoAnn Paules says:

    Knowing how to turn off error messages was one of the questions I missed on the Excel Expert exam. :-(

Leave a Reply to Debra Dalgleish Cancel reply

Your email address will not be published. Required fields are marked *