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
At the time of addition of drop down list on worksheet, or while performing any other data validation work then you can select the type of error like Stop, Warning or Information which you want to show.
By default the error type is selected STOP, this prevents you from entering any invalid data. Pop-up error message comes with buttons of Retry, Cancel & Help.
Here is the statement of popped-up error message.
“This value doesn’t match the data validation restrictions defined for this cell.”
Respond to the Error Message
If you also encounter such error message, you can any of buttons of Retry, Cancel & Help. Let’s take an idea what this buttons actually do.
this will take you to data validation page on the website of Microsoft. In the opened window you will get instruction regarding setting up the data validation cells. So you will not get any specific details why the entered is invalid.
Clean up the cell to enter new value or you can select from drop down list.
Your typed value in the cell gets highlighted, so that you can type new value. In case to use the drop down arrow, you need to clear the cell.
4: Customized Error Messages
Besides of using the default settings for data validation errors. You can go to error alert tab in data validation dialog box and properly customize them.
Select one of styles Stop, Warning or Information. After then enter a title and an error message which gives proper detail to user on what happened wrong.
In the above shown example, we have selected warning styles and entered a customized message. When we enter item which is not previously in the list? This had thrown a customized message pop-up within the warning icon along with the buttons.
5: Turn off Error messages:
If you want to completely turn off the data validation error then for that case.
On the Error Alert tab, remove check mark from “Show error alert after invalid data is entered”
6. Data Validation Error Messages in Tables
If in case you are using data validation in named Excel table, then an invalid data might flagged by Excel’s Error Checking Rules. Turning off error alert or setting the style to warning or information won’t resolve the issue.
In the below shown screenshot, you will see some warning on the cell C2, because it contain two item in the same cell.
Even by copying the cell C2 in some different location of worksheet or outside the table won’t remove the error message. This will affect your excel table a lot.
7. Turn Off Table Data Setting
If you are sure there is no issue with the cell’s data, then you can just ignore the error warnings. If it contains few messages then set the error warning in each cell manually, to remove the error.
- To close all the data validation warning in table, just follow down the steps mentioned below. In order to change the excel options.
- Hit the arrow present on the error alert and click error checking option.
- Now in the option window, go to the formula category and scroll down to error Checking Rules section.
- Un-check the check mark for “Data entered in a table is invalid”.
- Tap to the OK option.
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. In case of any excel corruption or file damage situation best recommended option to go with is MS Excel Repair Tool. This recovery tool works superb for restoring corrupt excel file and also in easy retrieving of data from excel worksheet like charts, cell comments, worksheet other data and 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.
If you know about any additional solutions or having any query regarding MS Excel then then please share it with us. You can go to the comment section or on MS Excel Ask Question page.