[9 Fixes] Excel This Value Doesn’t Match The Data Validation Error

Overview:

This article covers complete detail on how to fix this value doesn’t match the data validation error in Excel.

Apart from this, you will get to know other important details like what does this value doesn’t match the data validation restrictions error means and why data validation Is not working in Excel.

What Does “This Value Doesn’t Match The Data Validation Restrictions Defined For This Cell” Means?

If you are a regular Excel user then you are familiar with the Data Validation feature of the Excel function.  These are 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.

This particular Excel data validation error clearly indicates that the value assigned by you doesn’t match with the criteria defined using data validation for that particular cell.

Here is the screenshot when this error comes in Excel 2016:

this value doesn’t match the data validation error 

In Excel 2013:

This Value Doesn't Match The Data Validation Restrictions

To recover data from from corrupt Excel file, we recommend this tool:

This software will prevent Excel workbook data such as BI data, financial reports & other analytical information from corruption and data loss. With this software you can rebuild corrupt Excel files and restore every single visual representation & dataset to its original, intact state in 3 easy steps:

  1. Download Excel File Repair Tool rated Excellent by Softpedia, Softonic & CNET.
  2. Select the corrupt Excel file (XLS, XLSX) & click Repair to initiate the repair process.
  3. Preview the repaired files and click Save File to save the files at desired location.

Why Data Validation Is Not Working In Excel?

If data validation is not working in Excel then will frequently show errors like this value doesn’t match the data validation error.

Behind this Excel data validation error, following reasons are highly responsible. I have also mentioned a quick solution along with the reasons, so try them too.

1: Doesn’t Work For The Copied Data

Data validation is mainly used to restrict users from entering invalid data directly into the cell but won’t prevent them to copy it.

Well, there is no possible way to disable the shortcut of copy/paste, but you can prevent copying of data by simply drag-drop the cells.

  • For this, you need to go to the File tab and then hit the Options.
  • In the opened window of excel options, tap to the Advanced
  • Now from the Editing section uncheck the option “Enable fill handle and cell drag-and-drop“.

enable fill handle in excel 2

2: Won’t Work In The Cell Edit Mode

It is seen that the data validation command won’t work when the cell is already in edit mode means someone entering or modifying its data.

After completing the cell’s editing, just press the Enter or Esc button to quit edit mode. After that only start the data validation process.

3# Protected Or Shared Workbook

Data validation rules keep working in the shared and protected workbooks. It’s not that easy to set new rules or modify data validation settings.

For this, you need to unshare or unprotect Excel workbook first.

4# Incorrect Data Validation Formulas

If you are performing formula-based data validation in your Excel worksheet, then check these 3 criteria.

  • The data validation formula won’t give any error.
  • The formula doesn’t contain any mention about any empty cells.
  • Suitable cell references must be used.

5# Manual Recalculation Is Turned On

Keep the Manual Calculation mode enabled because an uncalculated formula stops data to get validated correctly.

For changing the Excel calculation option from automatic to manual

  • Go to the formula tab and then from the Calculation
  • Tap to the Calculation Options button and then hit the Manual option.

Set the Calculation Method to Manual 4

How 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: Remove The Data Validation
2: Input Messages
3: Data Validation Error Messages
4: Customized Error Messages
5: Turn Off Error Messages
6. Data Validation Error Messages in Tables
7. Turn Off Table Data Setting
8. A Method To Solve Problems of Error Alert
9: Fix Corrupt Excel File

1: Remove The Data Validation

  • Choose the cell in which you are getting data validation error.
  • Now go to the Data tab and click on the “data validation” icon from “data tools” group.
  • In the opened data validation window switch to the Settings tab.
  • Hit the Validation criteria section.
  • In order to disable the data validations choose the Any value option from the drop-down present in Allow section. After that hit the ok button.

2: Input Message

Input message appears when the user selects the cell and tells the user what to enter.

  • Choose the cell in which you are getting data validation error.
  • Now go to the Data tab and click on the “data validation” icon from the “data tools” group.
  • In the opened data validation window switch to the Input Message tab.
  • make a check across the ‘Show input message when the cell is selected’ option.
  • Enter a title and also an input message.

enter input message3

3: Data Validation Error Messages

At the time of addition of drop-down list on the 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 the popped-up error message.

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

this value doesn’t match the data validation error 

Respond to the Error Message

If you also encounter such error messages, you can any of the buttons of Retry, Cancel & Help. Let’s take an idea of what these buttons actually do.

Help –

this will take you to the data validation page on the website of Microsoft. In the opened window you will get instructions regarding setting up the data validation cells. So you will not get any specific details why the entered is invalid.

Cancel –

Clean up the cell to enter a new value or you can select from the drop-down list.

Retry –

Your typed value in the cell gets highlighted so that you can type a new value. In case to use the drop-down arrow, you need to clear the cell.

4: Customized Error Messages

Besides using the default settings for data validation errors. You can go to the error alert tab in the data validation dialog box and properly customize them.

Select one of the styles Stop, Warning, or Information. After then enter a title and an error message which gives proper detail to the user on what happened wrong.

errormessage 5

In the above-shown example, we have selected warning styles and entered a customized message. When we enter an item which is not previously on the list? This had thrown a customized message pop-up within the warning icon along with the buttons.

errormessage 6

5: Turn Off Error Messages

If you want to completely turn off the data validation error then for that case.

  • Choose the cell in which you are getting data validation error.
  • Now go to the Data tab and click on the “data validation” icon from “data tools” group.
  • In the opened data validation window switch to Error Alert tab, remove the checkmark from “Show error alert after invalid data is entered”

errormessage 7

6. Data Validation Error Messages in Tables

If in case you are using data validation in a named Excel table, then invalid data might be flagged by Excel’s Error Checking Rules. Turning off an error alert or setting the style to warning or information won’t resolve the issue.

In the below-shown screenshot, you will see some warnings on cell C2, because it contains two items in the same cell.

errorcheck 8

Even copying cell C2 in some different location of a 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 warnings in the 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 the error checking option.
  • Now in the options window, go to the formula category and scroll down to the Error Checking Rules section.
  • Un-check the checkmark for “Data entered in a table is invalid”.
  • Tap to the OK option.

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 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 Excel this value doesn’t match the data validation error. But if after following the solution you still getting the error message then, in this case, make use of the automatic solution to fix the error message.

9. Fix Corrupt Excel File

Chances are also that your Excel file is somehow got corrupted and thus the applied data validation rule is not working. To deal with any Excel corruption or file damages like situation, the best-recommended option is to go with is MS Excel Repair Tool.

This recovery tool works superbly for restoring corrupt Excel files and also in easy retrieving of data from Excel worksheets like charts, cell comments, worksheet other data, and properties. This is a professionally designed program that can easily repair .xls and .xlsx files.

* Free version of the product only previews recoverable data.

Steps to Utilize MS Excel Repair Tool:

g1
g2
g3
g4
g5
g6
g7

FAQ:

How To Create Data Validation Rule?

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

  • Choose the cell in which you are getting data validation error.
  • Now go to the Data tab and click on the “data validation” icon from “data tools” group.

click data validation1

  • In the opened data validation window switch to the Settings tab.
  • Now in the Allow list, click the whole number.
  • In the data list, click between.
  • Enter the Minimum and Maximum values.

validation criteria2

How To Update Or Remove Data Validation In An Inherited Workbook?

If you inherit the workbook along with the data validation, then you can easily remove or update only when your Excel worksheet is protected.

If your Excel worksheet is password protected then contact its previous owner to unlock Excel password.

Another option that you can try is copying the data to some other worksheet and after that removes the data validation.

If in case you are getting the data validation error then you can try entering or altering cell data. If you have doubts regarding what to enter, then contact the workbook owner.

When Is Data Validation Useful?

Data validation is most useful when you need to share the workbook with anyone else. Also when you want that your entered data is consistent and accurate.

Here are the lists of things you can easily perform with Data validation.

  • You can prevent someone from choosing a date beyond a certain time period.
  • Using this can set restrict entries to predefined items in a list and can also limit numbers outside the specified range.
  • It gives you the option to limit the text characters in a cell.
  • You can use the data validation based on values or formulas in other cells.

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 ample information about the error and fixes.

If you know about any additional solutions or having any queries regarding MS Excel then please share them with us. You can go to the comment section of this page.

Good Luck…



Margret Arthur is an entrepreneur & content marketing expert. She writes tech blogs and expertise on MS Office, Excel, and other tech subjects. Her distinctive art of presenting tech information in easy to understand language is very impressive. When not writing, she loves unplanned travels.