If you see the error “This value doesn’t match the data validation restrictions defined for this cell” when entering a value in an Excel cell, the solutions provided in this article will help. This error usually occurs when you enter a value other than the value set in the Data Validation criteria.
This value doesn’t match the data validation restrictions defined for this cell
You see the “This value doesn’t match the data validation restrictions defined for this cell” error in Excel when the value you enter in a cell does not match the values set for data validation. Use the following fixes to resolve this error:
- Check and edit the Data Validation values
- Clear all the Data Validation values
- Check for blank entries
- Disable the error message
Let’s start.
1] Check and edit the Data Validation values
You might have entered incorrect values for Data Validation due to which you are getting this error message while entering the required value in a cell. I suggest you check the Data validation values and edit them (if needed).
The following steps will help you with that:
- Select the cell or the range of cells that are giving you this error message.
- Select the Data tab from the Excel ribbon.
- Under the Data Tools group, click on the Data Validation option.
- Check the Data Validation value under the Settings tab. If the values entered there are incorrect, edit them and click OK.
2] Clear all the Data Validation values
If after trying the above fix, the error does not fix, you can try this. Clear all the Data Validation values for the targeted cell or the range of cells.
Open the Data Validation table by following the steps provided in the above fix. Under the Settings tab, click on the Clear All button. Click OK to save the changes. Now, open the Data Validation window again and enter the values in that table.
3] Check for blank entries
There might be blank entries or invisible spaces in the data you entered due to which Excel is showing you this error message. To check this, select the cell range and press the Ctrl + H keys. This will open the Find and Replace window. Now, enter space by pressing the spacebar at once in the Find what field. Click on the Find Next button to locate the invisible spaces in your entry. If you keep pressing the Find Next button, Excel will show you all the invisible spaces one by one.
Now, to erase all the invisible spaces, leave the Replace with field empty and click on the Replace All button. This action will erase all the invisible spaces from your data.
4] Disable the error message
You can also disable the error message. However, if you do this, The Data Validation feature will not work. Excel will not show you a warning message if the data you enter mismatches the data entered in the Data Validation table.
To disable this error message, open the Data Validation table and go to the Error Alert tab. Uncheck the following checkbox and click OK to save the changes.
Show error alert after invalid data is entered.
That’s it. I hope this helps.
How do I remove Data Validation restrictions from a cell?
You can remove the Data Validation restrictions from a cell or a range of cells by opening the Data Validation window. Select the cell or the range of cells and go to Data > Data Validation. Now, click on the Clear All button under the Settings tab.
How to see Data Validation restrictions for a cell?
To view the Data Validation restrictions for a cell or a range of cells in Excel, select the cell or the range of cells, then go to Data > Data Validation. In the Data Validation window, select the Settings tab to view the restrictions.
Read next: Unable to change date format in Excel.
