Duplicate entries may be valid — but when they aren't, they can derail your summaries and totals. Use Excel's Validation feature to reject duplicate entries.
- 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.