How to prevent Duplicates in a column in Excel with Data Validation!

Hey Data Validation Users,

There are N number of stuff you can do with Data Validation based on your creativity. Today I am going to show you how to prevent Duplicates in a column in Excel with the help of Data Validation.

Steps to Implement to prevent Duplicates in a column in Excel:

  1. Select the Entire Column where you want to prevent duplicates from being added.
  2. Got to Data—–> Data Validation from the Ribbon.
  3. Select “Custom” in “Allow:” field.
  4. Put the formula “=COUNTIF(A:A,A1)=1”

In other words we have instructed Excel to warn us if there is already the same entry in that particular column range.

As I always like to show it with GIF, here it is:

Prevent Duplicates in a column in Excel

Prevent Duplicates in a column in Excel

 

Remember if you are not selecting Entire Column then “A:A” range will change accordingly. But make sure that the range is made in Absolute reference. And PUT FIRST cell of the selection instead of “A1”, because A1 is Relative Reference. 

Download the Example file here. Prevent Duplicates in a column in Excel

To read all posts related to Data Validation Click here.

Make sure to share the blog with your Network.

Comment if you have any query implementing this solution.

Regards,

DJP

 

Share

Comments

Subscribe for Newsletter