Sometimes you just don't want data to repeat itself. On an order form or an inventory sheet, for instance, you may not want a part number noted in one cell to repeat in another cell. When you use Excel's Data Validation feature, you can prevent a value from appearing more than once in a range. For example, the range A2:A20 (illustrated) requires unique part numbers. If the user enters a number that already exists, a message box pops up and asks for a different one.

Caption: Use Excel's Data Validation feature to make sure you don't
enter the same part number twice in column A

To create a similar message box for your worksheet, start by highlighting the range of cells for which you need to punch in unique entries (here, the correct range to select is A2:A20). Then choose Data-Validation and click the Settings tab. Next, pick Custom from the Allow drop-down list. The Custom option requires a logical formula that will return either "True" or "False". In our example, we need a formula that will return "True" only if the content of the cell does not match one that already exists in the range. The COUNTIF function will do the job. Type the following formula into the Formula field: =COUNTIF($A$2:$A$20,A2)=1.

This formula counts the number of cells in range A2:A20 containing the same value that appears in cell A2. If the count is 1, the formula returns "True"; otherwise, it returns "False". Notice that the first argument for COUNTIF is an absolute reference to the entire validation range. In contrast, the second argument is a relative reference to the upper left cell in the validation range (it will be adjusted for each of the other cells in the range).

Next, to create the warning that appears in a pop-up message box when a duplicate value is entered into the selected range, click the Error Alert tab in the Data Validation dialogue box. For the Style, select Stop (this option rejects existing values). Enter a title for the message box (such as Duplicate Data) and type your error message. Now click OK and try it out. You'll find that you can enter any data into the validation range, but if you type an entry that already exists, you'll get the warning message. Click Retry to edit the cell's contents or choose Cancel to clear the cell.

While Data Validation is a useful feature, it contains a potentially serious design flaw. If you copy a cell and paste it to a cell that uses the feature, the Data Validation rules are wiped out. This problem also applies to cells that use Conditional Formatting. You'll need to keep this in mind when you're cutting and pasting in mission-critical applications.