Home > Validation > Rules > Duplicates Data Validation Custom Rules 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. Note: Data validation is not foolproof. It can be circumvented by pasting data into the cell, or by choosing Clear > Clear All, on the Ribbon's Home tab.

Prevent Duplicates

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.

How to Prevent Duplicates

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: Data validation is not foolproof. It can be circumvented by pasting data into the cell, or by choosing Clear > Clear All, on the Ribbon's Home tab.

TIP: To prevent duplicates based on entries in multiple columns, see these instructions

Create a Named Table

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

• Type the headings in row 1: EmpID, FName, LName and HireDate
• Type 2 rows of sample data
• Select a cell in the list, and on the Home tab of the Excel Ribbon, click Format as Table
• Click on one of the table styles
• Click OK to create the named table

The named table will have four columns, and drop down arrows in the header row.

Name the cells in EmpID column

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.

1. Select cells A2:A3
2. Click in the Name Box, to the left of the Formula Bar, and type a one-word name for the selected cells -- EmpIDs
3. Press the Enter key, to complete the naming.

Add the Data Validation

Next, follow these steps to create a custom data validation rule, to prevent duplicate IDs from being entered.

1. Select cells A2:A3
2. On the Ribbon's Data tab, click Data Validation
3. On the Settings tab, from the Allow drop down, choose: Custom
4. NOTE: If there could be blank cells in the column, remove the check mark from "Ignore Blank Cells", or duplicates will be allowed
5. In the formula box, you'll use the COUNTIF function to check value being entered in cell A2. It will be compared to all the entries in the EmpID column.
   To prevent duplicates, the count must be 1 or 0:
      =COUNTIF(EmpIDs,A2) <= 1

   

6. (optional) On the Error Message tab, add a title and error message. For example, Duplicate ID -- "That number has already been assigned."

Prevent Duplicates in Range of Cells

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.

1. Select cells B3:B10
2. On the Ribbon's Data tab, click Data Validation
3. On the Settings tab, from the Allow drop down, choose: Custom
4. For the formula in this example, use the COUNTIF function to check the range $B$3:$B$10, and count how many times the number in cell B3 appears. The formula's result must be 1 or 0:
      =COUNTIF($B$3:$B$10,B3) <= 1

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.

How to Prevent Duplicates for Multiple Columns

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.

First, name the cells in the three columns:

1. Select the data cells in the first column (not the heading cell)
2. Click in the Name Box, to the left of the Formula Bar, and type a one-word name for the range, such as ItemType
3. Press the Enter key, to complete the naming.
4. Repeat for the remaining columns, using other names, e.g. ItemSize and ItemColour

Add the Data Validation:

1. Select the data cells in all 3 columns (not the heading cells)
2. On the Ribbon's Data tab, click Data Validation
3. On the Settings tab, from the Allow drop down, choose: Custom
4. NOTE: If there could be blank cells in the columns, remove the check mark from "Ignore Blank Cells", or duplicates will be allowed
5. In the formula box, use the COUNTIFS function to check the combination of values being entered in row 2. It will be compared to all the combinations that are currently entered. To prevent duplicates, the count must be 1 or 0:
       =COUNTIFS(ItemType,A2,ItemSize,B2,ItemColour,C2)<=1

   

Require Unique 5-Digit Numbers

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:

=AND(ISNUMBER(A2),LEN(A2)=5,COUNTIF(EmpIDs,A2)<=1)

In this formula:

• COUNTIF function prevents duplicates
• ISNUMBER function prevents text entries
• LEN function checks the number of digits

Prevent Duplicates - Exception

Occasionally, you might want to prevent duplicates entries in a column, but allow a specific item to appear multiple times. In this example, unique product names are entered in column B. For new products, if the details aren't available yet, we enter "NEW" as the name, temporarily.

This data validation rule will allow multiple rows to have "NEW". All other entries in the product name column must be unique.

Add the Data Validation:

1. Select the product name cells - B4:B22
2. On the Ribbon's Data tab, click Data Validation
3. On the Settings tab, from the Allow drop down, choose: Custom
4. NOTE: If there could be blank cells in the columns, remove the check mark from "Ignore Blank Cells", or duplicates will be allowed
5. In the formula box, enter this formula, to check the entries:
       =OR(B4="NEW", COUNTIF($B$4:$B$22,B4) <=1)

In this formula:

• COUNTIF function prevents duplicates
• OR function allows the one exception -- "NEW"

Prevent Duplicates With Text Numbers

In this example, unique product names are entered in column B. The column is formatted as Text, and some of the product names are strings of numbers, such as "0123" or "123"

For this custom rule, we cannot use the COUNTIF function. Ittreats text numbers like real numbers, so it sees "0123" and "123" as both equal to the number 123.

Instead, we'll use SUMPRODUCT to check for duplicates. It will treat text numbers as text, so it can distinguish between "0123" and "123"

Add the Data Validation:

1. Select the product name cells - B4:B22
2. On the Ribbon's Data tab, click Data Validation
3. On the Settings tab, from the Allow drop down, choose: Custom
4. NOTE: If there could be blank cells in the columns, remove the check mark from "Ignore Blank Cells", or duplicates will be allowed
5. In the formula box, enter this formula, to check the entries:
       =SUMPRODUCT(- -($B$4:$B$22=B4)) <=1

Limit the Total

Prevent the entry of a value that will cause a range to go over a set total amount.

NOTE: To see another budget limit example, and a short video, go to the Limit Budget Entry Amounts page

In this example, the total budget cannot go over the amount that is entered in cell B10.

The budget amounts are in cells B2:B7, and the formula checks the total in those cells, when you add or edit an entry.

1. Select cells B2:B7
2. On the Ribbon's Data tab, click Data Validation
3. On the Settings tab, from the Allow drop down, choose: Custom
4. For the formula, use SUM to total the values in the range $B$2:$B$7. The result must be less than or equal to the limit entered in cell B10:
      =SUM($B$2:$B$7)<=$B$10

No Leading or Trailing Spaces

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.

1. Select cell B2
2. On the Ribbon's Data tab, click Data Validation
3. On the Settings tab, from the Allow drop down, choose: Custom
4. For the formula, enter:
      =B2 = TRIM(B2)

No Spaces in Text

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.

1. Select cell B2
2. On the Ribbon, click the Data tab, then click Data Validation
3. Choose Allow: Custom
4. For the formula, enter:
      =B2=SUBSTITUTE(B2," ","")

No Spaces in Cell

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.

Example 1

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.

1. Select cell D2
2. On the Ribbon, click the Data tab, then click Data Validation
3. Choose Allow: Custom
4. For the formula, enter:
      =LEN(D2)=LEN(SUBSTITUTE(D2," ",""))

Example 2

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.

1. Select cell F2
2. On the Ribbon, click the Data tab, then click Data Validation
3. Choose Allow: Custom
4. For the formula, enter:
      =ISERROR(FIND(" ",F2))

Prohibit Weekend Dates

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)

1. Select cell B2
2. On the Ribbon's Data tab, click Data Validation
3. On the Settings tab, from the Allow drop down, choose: Custom
4. For the formula, enter:

=WEEKDAY(B2,2) <6

Get the Sample Workbook

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.

Data Validation Tutorials

Data Validation Basics

Create Dependent Lists

Data Validation Tips

Limit Budget Entry Amounts

Data Validation Troubleshooting