Prevent duplicate Excel entries within a range

If you want to avoid duplicate entries within a range when you're working in Excel, then check out this tip from Mary Ann Richardson. You'll learn how to set it up and avoid redundancy.

Sometimes you may want to prevent users from entering duplicate values in a range of Excel cells. For example, you are entering price data for a number of items, and you want to make sure all item numbers entered in range A2:A52 are unique. To prevent duplicates, follow these steps:

  1. Select the range of cells that will contain the item numbers.
  2. Go to Data | Validation.
  3. Click the Settings tab.
  4. Under Validation Criteria, select Custom.
  5. Under Formula, enter =COUNTIF($A$2:$A$52,A2)=1.
  6. Click the Error tab.
  7. Under Title, enter Duplicate Entry.
  8. Under Error message, enter an appropriate message, such as: The value was already entered. All Item Numbers must be unique. Please try again.
  9. Click OK.

Now when the user attempts to enter a duplicate number, Excel will prevent the entry and alert the user with an error message.

Miss a tip?

Check out the Microsoft Excel archive, and catch up on our most recent Excel tips.

Help users increase productivity by automatically signing up for TechRepublic's free Microsoft Office Suite newsletter, featuring Word, Excel, and Access tips, delivered each Wednesday.

Editor's Picks

Free Newsletters, In your Inbox