Excel -- Data Validation -- Custom Validation Criteria Examples

Excel -- Data Validation -- Custom Validation Criteria Examples

Prevent Duplicates
Limit the Total
No Leading or Trailing Spaces
Prohibit Weekend Dates

Prevent Duplicates

Prevent duplicate entries in a range on the worksheet. In this example, Employee Numbers will be entered in cells B3:B10.

Select cells B3:B10

Choose Data|Data Validation

Choose Allow: Custom

For the formula, use COUNTIF to count the occurrences of the value in cell B3, in the range $B$3:$B$10. The result must be 1 or 0:
   =COUNTIF($B$3:$B$10,B3)<=1



Limit the Total

Prevent 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

Select cells C3:C7

Choose Data|Data Validation

Choose Allow: Custom

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



No Leading or Trailing Spaces

Prevent users from adding spaces before or after the text in the entry. The TRIM function removes spaces before and after the text. This formula checks that the entry is equal to the trimmed entry.

Select cell B2

Choose Data|Data Validation

Choose Allow: Custom

For the formula, enter:
   =B2=TRIM(B2)



Prohibit Weekend Dates

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

Select cell B2

Choose Data|Data Validation

Choose Allow: Custom

For the formula, enter:



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

1. Data Validation
2. Data Validation -- Create Dependent Lists
3. Hide Previously Used Items in a Dropdown List
4. Display Messages to the User
5. Use a List from Another Workbook
6. Validation Criteria Examples
7. Custom Validation Criteria Examples
8. Data Validation Tips
9. Data Validation Documentation
10 Data Validation -- Combo box
11. Data Validation -- Combo Box - Named Ranges
12. Data Validation -- Display Input Messages in a Text Box
13. Data Validation -- Dependent Dropdowns from a Sorted List

Home Excel Tips Excel Files Blog Contact

The Excel Store

Debra Dalgleish

Last updated: July 5, 2008 10:45 PM

Prevent Duplicates Prevent duplicate entries in a range on the worksheet. In this example, Employee Numbers will be entered in cells B3:B10. Select cells B3:B10 Choose Data\|Data Validation Choose Allow: Custom For the formula, use COUNTIF to count the occurrences of the value in cell B3, in the range $B$3:$B$10. The result must be 1 or 0: =COUNTIF($B$3:$B$10,B3)<=1
Limit the Total Prevent 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 Select cells C3:C7 Choose Data\|Data Validation Choose Allow: Custom 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)<=350
No Leading or Trailing Spaces Prevent users from adding spaces before or after the text in the entry. The TRIM function removes spaces before and after the text. This formula checks that the entry is equal to the trimmed entry. Select cell B2 Choose Data\|Data Validation Choose Allow: Custom For the formula, enter: =B2=TRIM(B2)
Prohibit Weekend Dates Prevent entry of dates that fall on Saturday or Sunday. The WEEKDAY function returns the number for the date entered, and values of 1 (Sunday) and 7 (Saturday) are not allowed. Select cell B2 Choose Data\|Data Validation Choose Allow: Custom For the formula, enter: