How to Fix ‘This Value Doesn’t Match the Data Validation Restrictions’ Error on Excel

Excel’s data validation feature is a powerful tool that allows you to control the type and range of data entered into a cell.

However, sometimes users encounter the “Value Doesn’t Match the Data Validation Restrictions” error, preventing them from inputting certain values. This Microsoft Excel error occurs when the data you are trying to input does not meet the criteria set by the data validation rules for that specific cell.

Fixing Microsoft Excel Error Message ‘This Value Doesn’t Match the Data Validation Restrictions’

This step-by-step tutorial will explore how to troubleshoot and fix this error to ensure smooth data entry in your Excel spreadsheets.

Step 1: Identify the Cell with Data Validation

The first step is to identify the cell that is causing the error. Excel highlights the cell with an error message, making it easier to spot.

• Look for a small triangle in the upper-right corner of the cell or a red exclamation mark next to the cell.
• Click on the cell with the error to reveal the error message.

Step 2: Review the Data Validation Rules

Once you have identified the cell with the error, it’s time to review the data validation rules applied to that cell. Data validation rules restrict data entry based on criteria such as whole numbers, decimal numbers, dates, or custom lists.

To view the data validation rules:

1. Select the cell with the error.
2. Go to the “Data” tab on the Excel ribbon.
3. Click on “Data Validation” in the Data Tools group.

The Data Validation dialog box will appear, showing the validation criteria.

Step 3: Correct Data Entry

After reviewing the data validation rules, compare them to the data you are trying to input. Ensure that the data you are entering meets the specified criteria.

If the data you are entering doesn’t match the data validation rules, you have two options:

• Adjust the Data Validation Rules – If the data you want to enter is valid and should be allowed according to your requirements, you can modify the data validation rules to accommodate the new data. In the Data Validation dialog box, update the criteria to match the desired data entry. Click “OK” to save the changes.

• Enter Valid Data – If the data you want to input doesn’t meet the current data validation rules, you must enter valid data that complies with the restrictions. For instance, if the cell allows only whole numbers between 1 and 100 and you are trying to enter 150, you must enter a number between 1 and 100 to resolve the error.

Step 4: Remove Data Validation (Optional)

In some cases, you may find that the data validation rules are too restrictive or unnecessary. If you want to remove data validation from the cell, follow these steps:

1. Select the cell with data validation.
2. Go to the “Data” tab on the Excel ribbon.
3. Click on “Data Validation” in the Data Tools group.
4. In the Data Validation dialog box, select the “Settings” tab.
5. Choose “Any Value” from the “Allow” drop-down menu.
6. Click “OK” to remove the data validation.

Step 5: Apply Data Validation to Other Cells (Optional)

Suppose you have fixed the error and are satisfied with the data validation rules. In that case, you might consider applying the same data validation settings to other cells with similar data entry requirements. That can help maintain consistency and accuracy in your spreadsheet.

Try to follow the solutions above and check if it fixes the error message “This Value Doesn’t Match the Data Validation Restrictions” on Microsoft Excel.