Contextures

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.

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: To prevent duplicates based on entries in multiple columns, see the instructions below

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
  • set up a named table

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

set up a named table

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.

set up a named table

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

    create a custom rule for no duplicates in a table column

  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
  5. 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.

create a custom rule for no duplicates in a list column

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

    create a custom rule for no duplicates in multiple columns

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

custom rule for 5-digit number, no duplicates

Limit the Total

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.

  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 $C$3:$C$7. The result must be less than or equal to the limit entered in cell B10:
       =SUM($B$2:$B$7)<=$B$10

custom rule for total amount maximum

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)

custom rule for no leading spaces

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," ","")

custom rule for no leading spaces

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," ",""))

custom rule for no spaces

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))

custom rule for no spaces - example 2

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)

WEEKDAY function return type 2

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

custom rule for no weekend dates

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.

Get All the Excel News

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

Search Contextures Sites

Data Validation Tutorials

Data Validation Basics

Create Dependent Lists

Create Date Rules

Dependent Dropdowns from a Sorted List  

Dependent Lists With INDEX  

Hide Previously Used Items in a Dropdown List

Display Messages to the User

Display Input Messages in a Text Box 

Use a List from Another Workbook

Data Validation Tips

Data Validation Documentation

Search Contextures Sites

 

Excel Data Entry Popup List

 

 

 

 

Excel Data Entry Popup List

 

 

Excel Data Entry Popup List

Last updated: August 31, 2017 1:25 PM