Duplicates aren’t always a bad thing — sometimes they’re valid entries. But when they’re not, duplicate values return erroneous summaries and totals. The good news is that you can easily prevent duplicates in a specific range using Excel’s Validation feature as follows:
- Select the range of cells in which you want to prevent duplicate values.
- Choose Validation from the Data menu. In Excel 2007, click the Data tab and choose Data Validation from the Data Validation option’s drop-down list (in the Data Tools group).
- Click the Settings tab.
- Choose Custom from the Allow drop-down list.
- Enter a formula in the following form into the Formula control:=COUNTIF($range,firstcellinrange) = 1
- Click the Error Alert tab.
- Enter the text Duplicate Entry in the Title control.
- In the Error Message box, enter a meaningful description, such as The value is a duplicate value and therefore, not valid.
- Click OK.
If you attempt to enter a duplicate value, Excel will reject it. When this happens, click Cancel to clear the error message and enter a valid value.
