Scott Crevier's General Help Resource: Q4038: How can I prevent duplicate entries in a Microsoft Excel spreadsheet?

Scott Crevier's General Help Resource

Q4038: How can I prevent duplicate entries in a Microsoft Excel spreadsheet?

Document #

Fri 05-Jul-2002 2:12pm

To prevent duplicate entries in a column of an Excel spreadsheet, do the following:

Click on the heading of the column where you want to prevent duplicates. For example, in my spreadsheet, I don't want dupes in column C, so I click on the "C" column heading. This should highlight the entire column, with only the first cell in the column (C1) being active.
Select "Validation" from the "Data" menu.
In the "Allow" field, select "Custom" from the list.
Enter the following forumula as shown in the screen shot:
=COUNTIF($C$1:$C$2000,C1)=1

Note that the first two cell references in the forumula are absolute (by using the $). The cell range of $C$1:$C$2000 means that those are the cells whose values are checked against your current entry. In my example, I'll never have more than 2000 entries, so this will work just fine. For your use, be sure to enter a number high enough to cover the number of rows you'll have.
Click on the "Input Message" tab and make sure the "Show input message" checkbox is not checked.
Click on the "Error Alert" tab. Check the "Show error" check box. Choose a style. In my case, I only want to warn the user, which means that dupes will be allowed, but at least I'll be warned if a duplicate is entered. Then enter the error message as shown:
Click "OK". That's it. Now go ahead and make a duplicate entry and see what happens. Of course you can go back and tweak your validation to make it do exactly what you want.

Tue 10-Jun-2003 CST

Scott Crevier • De Pere, Wisconsin, USA