If users ignore the input message and enter a number that is not valid, you can show them an error alert. Check 'Show error alert after invalid data is entered'.
You can use Excel's Go To Special feature to quickly select all cells with data validation.
Result: Note: to remove data validation from a cell, select the cell, on the Data tab, in the Data Tools group, click Data Validation, and then click Clear All.
You can use data validation to restrict the type of data or the values that users enter into a cell.
Then, use that list as the source for the Data Validation drop down list.
If you don't want to create a named table, you can follow the instructions in the named range section below.
In the Go To Special dialog box, check the Data validation and Same options, and finally click the OK button.
Input messages appear when the user selects the cell and tell the user what to enter. Watch the steps in this short video, and the written instructions are below the video Instead of referring to a list of items on the worksheet, you can type the list in the Source box, separated by commas.For example: Yes, No, Maybe Notes: To allow users to also type items that are not in the list, remove the check mark to turn off the Error Alert.When you create a formatted Excel table, you can use data validation to prevent duplicates within a column.In this example, Employee ID is entered in the first column, and each ID must be unique. There are also instructions below for how to prevent duplicates based on entries in multiple columns When you create a formatted Excel table, you can use data validation to prevent duplicates within a column.