How to create your own Excel data validation custom rules, with custom criteria and formulas. Prevent duplicates, or limit the total in a range of cells, and more examples.
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. The written instructions are below the video.
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.
NOTE: To prevent duplicates based on entries in multiple columns, see the instructions below
Follow these steps, to set up a named table, like the one shown below.
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 EmpID column. Because it is based on a named table, the named range will automatically adjust in size, when you add or delete rows.
Note: An Excel name must be one word -- no spaces.
Next, follow these steps to create a custom data validation rule, to prevent duplicate IDs from being entered.
If you're not using an Excel table, as shown above, you can prevent duplicate entries in a specific range on the worksheet. In this example, Employee ID Numbers will be entered in cells B3:B10. Use a custom rule to prevent the same number from being entered twice, in those cells.
NOTE: The range is entered as an absolute reference -- $B$3:$B$10 -- so it stays the same, in all of the cells with that custom rule. The cell with the value (B3) is a relative reference -- it should change in each cell.
The examples above check the contents of one column, to prevent duplicates. In this example, three columns will be checked, to make sure that combination has not been entered before. The columns are ItemType (A), ItemSize (B), and ItemColour (C). Data validation will be applied in all three columns.
The Employee ID example shown above prevents duplicate entries. 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.
To create the data validation, follow all the steps in the Employee ID example, and use this formula, instead of the original one:
In this formula:
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. The budget amounts are in cells B2:B7, and the formula checks the total in those cells, when you add or edit an entry.
You can prevent users from adding spaces before or after the text in the entry. The TRIM function removes spaces before and after the text, and any extra spaces within the text.
The formula in this example checks that the entry in cell B2 is equal to the trimmed entry in that cell.
Thanks to Jerry Latham for this example.
Use this custom rule to prevent users from adding ANY spaces in a text string. The SUBSTITUTE function replaces each space character - " " - with an empty string - ""
The formula in this example checks that the entry in cell B2 is equal to the entry without spaces.
You can prevent users from adding ANY spaces in the cell -- whether the entry is text, numbers, or a combination of both. Here are two formulas that check for spaces.
Thanks to Jerry Latham for this example.
The LEN function counts the number of characters entered in cell D2, and compares that to the number of characters after SUBSTITUTE removes the space characters.
The FIND function looks for the space character - " " - and the ISERROR function result is TRUE, if the space character is not found.
The formula in this example checks that the entry in cell F2 is equal to the entry without spaces.
There is a built-in rule setting for Dates, where you can set a date range for the data entry cells. The dates can be typed into the rule boxes, or you can refer to cells where the minimum and maximum dates are entered. You can also use a formula with the built-in date rule, such as =TODAY()-30. That creates a dynamic date range.
For date rules that do not require a specific date range, create a custom rule. For example, this rule will prevent the entry of dates that fall on Saturday or Sunday.
The WEEKDAY function returns the weekday number for the date entered. Our formula will use the return_type argument of 2, which returns 1 - 7 for Monday through Sunday. That makes it easy to block 6 (Saturday) and 7 (Sunday)
Our formula will allow numbers lower than 6 (Saturday)
Get the free Excel workbook with the Data Validation Custom Rules examples. The zipped file is in xlsx format, and does not contain any macros.
For regular Excel news, tips and videos, please sign up for the Contextures Excel newsletter. Your email address will never be shared with anyone else.
Search Contextures Sites
More Data Validation Links
Last updated: August 31, 2017 1:25 PM