Also remember that the validation you enter must be consistent with the validation type you selected.
Topics: You create a validation by running the Create Validation Wizard.
When you create a formatted Excel table, you can use data validation to prevent duplicates.
In this example, an Employee ID is entered in the first column, and each ID must be unique. When you create a formatted Excel table, you can use data validation to prevent duplicates within a column.
To create the data validation, follow all the steps in the Employee ID example, and use this formula, instead of the original one: =AND(ISNUMBER(A2), LEN(A2)=5, COUNTIF(Emp IDs, A2) Prevent the entry of a value that will cause a range to go over a set total amount.
In this example, the total budget cannot go over the amount that is entered in cell B9.
In this example, Employee ID is entered in the first column, and each ID must be unique.
The formula in this example checks that the entry in cell B2 is equal to the trimmed entry in that cell. Use this custom rule to prevent users from adding ANY spaces in a text string.Note: If you aren't using a named table, follow the instructions to Prevent Duplicates in a Range The named table will have four columns, and drop down arrows in the header row.Next, follow these steps to name the items in the Emp ID column.However, it doesn't limit the type of entry that can go into the Employee ID cell.In this example, the rule is revised, to require a 5-digit number, with no duplicates.
For example, this rule will prevent the entry of dates that fall on Saturday or Sunday.