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.
Related Topics:Enterprise Software Developer Open Source Software Mobility Cloud
Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.