Stuck with MS Excel Data Validation Error? Here is the Quick Fix to Get You Going!

If you use a Excel application frequently then you must be familiar with the feature of excel function that is “Data Validation”.  In excel 2010 by using data validation feature users can ensure the data-type integrity by enforcing users to enter valid data from the range you have been selected off. Likewise you can also input message that appears before entering data, it helps users to explain with valid entries and error message that appear in case of any invalid data entered. In this tutorial we will talk about how to set error alerts for cell correctly.

Manual Solution To Resolve MS Excel Data Validation Error

Create Data Validation Rule

To create the data validation rule, execute the following steps.

  1. Select cell C2.
  2. On the data tab, click data validation.

click data validation1

On the Settings tab:

Not happy with your MS Excel performance?
Don't Miss The BEST TOOL To FIX .xls/.xlsx Errors & Issues
  1. In the allow list, click whole number.
  2. In the data list, click between.
  3. Enter the Minimum and Maximum values.

validation criteria2

Input Message

Input message appear when the user selects the cell and tell the user what to enter. On the

Input Message tab:

  1. Check ‘Show input message when cell is selected’.
  2. Enter a title and also an input message.

enter input message3

Also Read:

How To Use Dynamic Data Labels To Create Interactive Excel Charts

How To Fix “Too Many Different Cell Formats” Error Message In Excel

Data Validation Error Messages

If you add a drop list on a worksheet or any other type of data validation, you have the option to select for the different error alert message like Stop, Warning or Information.

The default one is the Stop and that prevent you from entering the invalid data. An error message appears with the button for Retry, Cancel and Help.

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

errormessage4

Respond to the Error Message

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

  • Help– Takes you to data validation page on the Microsoft website, where there are instructions for settings 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 that you can type a new value or select from the drop down list, if there is one.
  • Retry– Highlight the value which you have typed in the cell, so that 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 check out the Error Alert tab in the data validation dialog box, and customize them.

Choose one the styles and enter a suitable title and error message that will best define other on what went wrong.

errormessage 5

Suppose you have selected warning style and entered a customized message. Now if you enter an item that is not in the list the customized message appears along with a Warning icon, and different buttons.

errormessage 6

Turn off error messages:

If in case you desire to turn off the data validation error alerts completely then also you have the option.

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

errormessage 7

Data Validation Error Messages in Tables

If you are 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 shown below, there is a warning  on cell C2, because two items are in the cell.

errorcheck 8

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

Turn Off Table Data Setting

If you think that there is not any problem with the cells data, you can simply ignore the error warnings. Or if there are just a few messages, you can set the error message 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 given below, to change one of the excel options.

  • Click the arrow on the error alert and click error Checking Options
  • In the option 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, that excel will stop flagging ALL data errors in your tables, in ALL workbooks that you open.

errorcheck 9

A Method to Solve Problems of Error Alert

In the above part, we have figured out the different styles of error alert and you know that in some circumstances, the invalid value will remain in the cells.  Therefore to handle this issue you can use the additional features to check for the errors.

  1. Click the tab “data” in the ribbon.
  2. And then click the small arrow next to the button “Data Validation”.
  3.  In the drop down menu click the option “Circle Invalid Data”.

circle invalid data 10

4. After then you will see that a window will appear on your screen. If you want to get more accurate result then click “yes” in the window.

click yes to get accurate result11

5. This will highlight all the invalid content within the red oval circle.

red oval12

But if you copy the format into B2, the cell is actual an ordinary cell. Therefore even if the content is invalid, it will not be highlighted with a red oval. Hence you may pay more attention when paste values from other cells.

 

2415 Total Views 9 Views Today

Stuck With MS Excel Data Validation Error? Here Is The Quick Fix To Get You Going!