How do you match the Data Validation restrictions defined for this cell?
Do you like to use error checking in Excel, so that problem cells are flagged, or do you turn that feature off? There are options for data validation error messages too – do you use those? Show
Data Validation Error MessagesIf you add a drop down list on a worksheet, or any other type of data validation, you can choose which type of Error Alert messages should be shown – Stop, Warning or Information. The default setting is Stop, and that prevents you from entering invalid data. An error message appears, with buttons for Retry, Cancel and Help. “This value doesn’t match the data validation restrictions defined for this cell.” Respond to the Error MessageIf you see that error message, what happens if you click one of the buttons?
Customized Error MessagesInstead of leaving the default settings for data validation errors, you can go to the Error Alert tab in the data validation dialog box, and customize them. Choose one of the Styles, and enter a Title and Error message that will give people details on what went wrong. In this example, I selected Warning style, and entered a customized message. When I enter an item that isn’t in the list, the customized message appears, with a Warning icon, and different buttons. Turn Off Error MessagesIn some cases, you might want to turn off the data validation error alerts completely, like I do when allowing multiple selections from a drop down list. On the Error Alert tab, remove the check mark from “Show error alert after invalid data is entered” Data Validation Error Messages in TablesIf you’re using data validation in a named Excel table, invalid data might be flagged by Excel’s Error Checking Rules, even if you turned off error alerts, or set the Style to Warning or Information. In the screen shot below, there is a warning on cell C2, because two items are in the cell. If you copied cell C2 to a different part of the worksheet, outside of a table, the error warning would disappear – it only affects tables. Turn Off Table Data SettingIf you know that there isn’t really a problem with the cell’s data, you can simply ignore the error warnings. Or, if there are just a few messages, you can set the error warning in each cell manually, to Ignore Error. If you want to turn off all of the data validation warnings in tables, you can follow the steps below, to change one of the Excel options.
Keep in mind though, that Excel will stop flagging ALL data errors in your tables, in ALL workbooks that you open. You can read the details on my website. Video: Data Validation MessagesTo see the steps for creating a data validation input message or error message, watch this short video tutorial. _______________ When you try to input some value in a cell and you get an error message in Excel 2016: or in Excel 2013: 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:
How do I match data validation in Excel?Try it!. Select the cell(s) you want to create a rule for.. Select Data >Data Validation.. On the Settings tab, under Allow, select an option: ... . Under Data, select a condition.. Set the other required values based on what you chose for Allow and Data.. How do you fix the value doesn't match the data validation restrictions defined for this cell?The value you entered is not valid.. Select the cell or cells that you want to remove validation from.. Click Data > Data Tools > Data Validation.. Click the Clear All button.. Click OK.. How do I unlock data validation restrictions in Excel?How to remove data validation in Excel. Select the cell(s) with data validation.. On the Data tab, click the Data Validation button.. On the Settings tab, click the Clear All button, and then click OK.. |