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

If you are a regular Excel user then you are familiar with the Data Validation feature to Excel function.  This is very helpful features of Excel. In Excel 2010 the data validation feature allows the users to ensure the data-type integrity by enforcing users to enter valid data from the range you have been selected off. Likewise, the user 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 article, we are describing how to fix this value doesn’t match the data validation error in Excel and how to set error alerts for cell correctly.

 

Solution to Fix Excel this value doesn’t match the data validation Error 

 
Here follow the possible workarounds to fix This value doesn’t match the data validation restrictions defined for this cell error in Excel.

1: 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

Not happy with your MS Excel performance?
Don't Miss The BEST TOOL To FIX .xls/.xlsx Errors & Issues

On the Settings tab:

  1. In the Allow list, click the whole number.
  2. In the data list, click between.
  3. Enter the Minimum and Maximum values.

validation criteria2

Also Read:

 

2: Input Message

 

Input message appears when the user selects the cell and tell the user what to enter. On the Input Message tab:

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

enter input message3

Also Read:

 

3: 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.”

Respond to the Error Message

If you see This value doesn’t match the data validation error message, then know what happens 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.

 

4: 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

 

5: 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

 

6. 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 to cell C2 to a different part of the worksheet, outside of a table, the error warning should disappear- it only affects tables.

 

7. Turn Off Table Data Setting

 

If you think that there is not any 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 message in each cell manually, to ignore This value doesn’t match the data validation 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 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, that excel will stop flagging ALL data errors in your tables, in ALL workbooks that you open.

errorcheck 9

 

8. 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 a 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 actually 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 pasting values from other cells.

These are the manual solution to fix the Excel this value doesn’t match the data validation error. But if after following the solution you still getting then error message then, in this case, make use of the automatic solution to fix the error message.

 

Automatic Solution: MS Excel Repair Tool

 

The above mentioned manual solution will most probably sort out the issues and mentioned errors from Excel file. But for any other corruption or file damage most suitable option would be to make use of MS Excel Repair Tool. This is is highly competent in restoring corrupt Excel files and also retrieves data from worksheet like cell comments, charts, other data and worksheet properties. This is a professionally designed program that can easily repair .xls and .xlsx files.

down-inbuy-now

 

Steps to Utilize MS Excel Repair Tool:

g1
g2
g3
g4
g5
g6
g7

Conclusion 

 

Thanks for Reading the article. Hope after following the solution given in the article you are able to fix This value doesn’t match the data validation error. I tried my best to provide the ample information about the error and fixes.

However, if you are having any additional fixes or any query then please share it with us. You can go to the comment section or on MS Excel Ask Question page.

Good Luck…

down-inbuy-now

Summary
Stuck With MS Excel Data Validation Error? Here Is The Quick Fix To Get You Going!
Article Name
Stuck With MS Excel Data Validation Error? Here Is The Quick Fix To Get You Going!
Description
Are you the one facing Excel Data Validation Error then here follows the complete solution to fix the error this value doesn’t match the data validation in Excel
Author
Publisher Name
Repair MS Excel Blogs
Publisher Logo

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