Hints - Microsoft Excel - 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. 

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 

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.

No Spaces in Text

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.

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

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. 

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.

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:

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.

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.