Contextures

Data Validation Examples Custom Criteria

When the built-in rules don't do what you need, use custom criteria in data validation, to create special rules, such as preventing duplicates, or limiting the total in a range of cells.

Prevent Duplicates in Table Column

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. The written instructions are below the video. There are also instructions below for how to prevent duplicates based on entries in multiple columns

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. If you want to prevent duplicates based on entries in multiple columns, see the instructions below

First, name the cells in the first column:

  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 range, e.g. EmpIDs
  3. Press the Enter key, to complete the naming.

Add the Data Validation:

  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

    prevent duplicates in column

  6. (optional) On the Error Message tab, add a title and error message, e.g. 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 Numbers will be entered in cells B3:B10, and you can 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, we use the COUNTIF function to count the occurrences of the value in cell B3, in the range $B$3:$B$10. The formula's result must be 1 or 0:
       =COUNTIF($B$3:$B$10,B3) <= 1

data validation 01

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 Main Product (A), Sub Product (B), and ProdCode (C). Data validation will be applied in the ProdCode column.

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, e.g. MainProd
  3. Press the Enter key, to complete the naming.
  4. Repeat for the remaining columns, using other names, e.g. SubProd and ProdCode

Add the Data Validation:

  1. Select the data cells in the ProdCode column (not the heading cell)
  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, 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(MainProd,A2,SubProd,B2,DesCode,C2)<=1

    prevent duplicates in column

  6. (optional) On the Error Message tab, add a title and error message, e.g. Unique Code -- "Enter a Product Code that has not been used for this main product/sub product"

Require Unique 5-Digit Numbers

The custom data validation example shown above prevents duplicate entries. However, it doesn't limit the type of entry that can go into the cell. In this example, the formula is revised, to require 5-digit numbers, with no duplicates.

To create the data validation, follow all the steps in the example above, and use this formula, instead of the one shown above:

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

The COUNTIF function prevents duplicates, the ISNUMBER function prevents text entries, and the LEN function checks the number of digits.

Limit the Total

Prevent the entry of a value that will cause a range to exceed a set total. In this example, the total budget cannot exceed $3500. The budget amounts are in cells C3:C7, and the formula checks the total in those cells

  1. Select cells C3:C7
  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 $3500:
       =SUM($C$3:$C$7) <= 3500

data validation 02

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)

data validation 03

No Spaces in Text

Thanks to Jerry Latham for this example.

You can 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 B3 is equal to the entry without spaces.

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

data validation no 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 B3, and compares that to the number of characters after SUBSTITUTE removes the space characters.

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

data validation 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 B3 is equal to the entry without spaces.

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

data validation no spaces

Restrict to a Specific Date Range

With data validation, you can require dates in a cell, and specify which dates are valid.

To set a specific date range, follow these steps:

  1. Select the cell where you want the data validation applied
  2. On the Ribbon, click the Data tab, then click Data Validation
  3. From the Allow drop down, select Date
  4. In the Data drop down, select one of the operators, such as Greater Than, or Between
  5. Depending on the operator you selected, Start Date and/or End Date boxes will appear
  6. Type a date in the Start Date and/or End Date boxes, OR refer to a worksheet cell that contains the start or end date.
  7. (optional) Add an Input Message, to explain the data validation rule when the cell is selected
  8. (optional) Change the Error Alert setting, so it shows a message, or warns users about invalid data.
  9. Click OK, to close the dialog box.

    data validation date range

Restrict to a Dynamic Date Range

Instead of setting a specific date range, you can create a dynamic range. In this example, a formula is used in the date box, to allow only dates within a specific number of days from the current date.

  1. Select the cell where you want the data validation applied
  2. On the Ribbon, click the Data tab, then click Data Validation
  3. From the Allow drop down, select Date
  4. In the Data drop down, select one of the operators, such as Greater Than, or Between
  5. Depending on the operator you selected, Start Date and/or End Date boxes will appear
  6. Type a formula in the Start Date and/or End Date boxes, using the TODAY function. For example,
  7. To set the earliest start date allowed as 60 days prior to the current date:

    =TODAY()-60

    To set the latest start date allowed as 60 days after the current date:

    =TODAY()+60

  8. (optional) Add an Input Message, to explain the data validation rule when the cell is selected
  9. (optional) Change the Error Alert setting, so it shows a message, or warns users about invalid data.
  10. Click OK, to close the dialog box.

restrict date range

Prohibit Weekend Dates

Prevent entry of dates that fall on Saturday or Sunday. The WEEKDAY function returns the weekday number for the date entered, and values of 1 (Sunday) and 7 (Saturday) are not allowed.

  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:

=AND(WEEKDAY(B2) <> 1, WEEKDAY(B2) <> 7)

data validation 04

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

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: June 3, 2016 10:30 AM