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.
- Select cell C2.
- On the data tab, click data validation.
On the Settings tab:
- In the Allow list, click the whole number.
- In the data list, click between.
- Enter the Minimum and Maximum values.
2: Input Message
Input message appears when the user selects the cell and tell the user what to enter. On the Input Message tab:
- Check ‘Show input message when the cell is selected’.
- Enter a title and also an input message.
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.
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.
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.
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.
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.
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.
- Click the tab “data” in the ribbon.
- And then click the small arrow next to the button “Data Validation”.
- In the drop down menu click the option “Circle Invalid Data”.
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.
5. This will highlight all the invalid content within the red oval circle.
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.
Steps to Utilize MS Excel Repair Tool:
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.