MS Excel tip used to prevent duplicates


The below example shows how to prevent duplicates in a range of cells (A2:A20)

The below example shows how to prevent duplicates in a range of cells (A2:A20):

• Select a range of cells, for example, A2:A20;
• Select Data -> Data Validation (in Data Tools Group);
• In Data Validation dialog box, select Settings -> Allow -> Custom;
• In the Formula box, enter the following formula:


=COUNTIF($A$2:$A$20,A2)=1

• Select the Error Alert tab;
• In the Title box, enter: Duplicate Entry;
• In the Error message box, enter: The value you entered already appears in the list above;
• Click OK.


Comments

No responses found. Be the first to comment...


  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
  • Name:
    Email: