Solved: This value doesn’t match the data validation restrictions defined for this cell

by

When you try to input some value in a cell and you get an error message in Excel 2016:

This value doesn't match the data validation restrictions defined for this cell.
Excel 2016 - This value doesn't match the data validation restrictions defined for this cell.

or in Excel 2013:

The value you entered is not valid. A user has restricted values that can be entered into this cell.
Excel 2013 - The value you entered is not valid. A user has restricted values that can be entered into this cell.

This means that someone has set Data Validation on that cell. To view its configuration and remove if needed follow these steps:

  1. Select cell and go to Data -> Data Validation
    Depending on the size of the window you access it in a bit different way.
    Please look at the screenshots below:

    Excel - Data -> Data Validation - Narrow Size Window. Click to enlarge.
    Excel - Data -> Data Validation - Narrow Size Window. Click to enlarge.
    Excel - Data -> Data Validation - Middle Size Window. Click to enlarge.
    Excel - Data -> Data Validation - Middle Size Window. Click to enlarge.
    Excel - Data -> Data Validation - Wide Size Window. Click to enlarge.
    Excel - Data -> Data Validation - Wide Size Window. Click to enlarge.

  2. You will see Data Validation window. Look into Settings tab, Validation criteria section.
    If you want to disable validation, select Any value in Allow drop down and accept by clicking OK.

    Excel - Data Validation Window - Settings - Criteria.
    Excel - Data Validation Window - Settings - Criteria. Click to enlarge.

12 thoughts on “Solved: This value doesn’t match the data validation restrictions defined for this cell”

  3. Odd validation situation:

    I have a cell that is restricted to value of a list (win, lose, draw).

    I have another cell that is also restricted to this same list.

    If a value is chosen from the list in the second cell, I want the first cell to assume that value, otherwise, I would like it to remain empty until such time as a value is selected in its dropdown. (I would prefer that once it assumes the value of the second cell, the dropdown goes away, but this is not critical)

    Any ideas?

    Thanks in advance!

    Reply

  4. Thank you!! I started a data validation in this cell but didn’t need to. I am learning and when trying to set up my VLOOKUP in the same cell,
    I couldn’t figure out why this message popped up. I read your note and realized I needed to remove the data validation! Thank you!!!

    Reply

  7. Are you actually retarded?
    How is this “solving an error”??
    Data Validation is probably in most cases working exactly as intended, and your “solution” is to turn it off?? FMD

    Reply

    • Main solution is to check what kind of validation is configured so correct value can be entered.
      Description of disabling validation is an option for some users if needed as text states: “If you want to disable validation…”

      Reply

    • Why would you be so obtuse and rude? Let me lay down some scenarios for you. What if you like the format of the excel spreadsheet and didn’t need the data validation, what if someone started the creation of the spreadsheet and left the company before finishing it? What if………? It’s really sad that such a benign Q and A on a site that is meant to be informative and helpful can TRIGGER such a negative response. Be helpful or be quite.

      Reply

Leave a Comment