Search Contextures Sites

More Data Validation Links

Data Validation Basic

Create Dependent Lists

Use a List from Another Workbook

Criteria Examples

Data Validation Tips

 

Contextures
Excel news
by email

 

 

 

Learn how to create Excel dashboards.

 

 

 

 

 

Learn how to create Excel dashboards.

 

 

30 Excel Functions in 30 Days

 

 

Learn how to create Excel dashboards.

 

 

 

Live-link your Excel dashboards to nearly any web data.

 

Learn how to create Excel dashboards.

Data Validation Examples Custom Criteria

Video: Prevent Duplicates in Table Column
Prevent Duplicates in Table Column
Prevent Duplicates in Range of Cells
Require Unique 5-Digit Numbers
Limit the Total
No Leading or Trailing Spaces
No Spaces in Text
Restrict to Specific Date Range
Restrict to Dynamic Date Range
Prohibit Weekend Dates

Video: 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.

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.

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
  6. prevent duplicates in column

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

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.

Prevent Duplicates in Range of Cells

You can use Data Validation to prevent duplicate entries in a 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. Choose Data|Data Validation
  3. Choose Allow: 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

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. Choose Data|Data Validation
  3. Choose Allow: 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. Choose Data|Data Validation
  3. Choose Allow: 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. Choose Data|Data Validation
  3. Choose Allow: 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
Criteria Examples
Custom Criteria Examples
Data Validation Tips
Data Validation Documentation
Data Validation Combo Box     
Data Validation Combo Box - Named Ranges
Data Validation Combo Box -- Click
Data Validation - Add New Items  

 

Privacy Policy

 

Contextures Inc., Copyright 2014
All rights reserved.

 

Last updated: October 22, 2014 7:50 PM