Prevent duplicates when entering data into Excel

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.

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:

  1. Select the range of cells in which you want to prevent duplicate values.
  2. 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).
  3. Click the Settings tab.
  4. Choose Custom from the Allow drop-down list.
  5. Enter a formula in the following form into the Formula control:=COUNTIF($range,firstcellinrange) = 1

  6. Click the Error Alert tab.
  7. Enter the text Duplicate Entry in the Title control.
  8. In the Error Message box, enter a meaningful description, such as The value is a duplicate value and therefore, not valid.
  9. 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.


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.

Editor's Picks

Free Newsletters, In your Inbox