Duplicates sometimes can be a bad thing which leads to mistakes and wrong calculation. In this article, I will show you how to set a no duplicates rule to cells. In that case, Excel users are not allowed to put duplicate value into specific cells.
Preventing duplicate entries in Excel is not as difficult as you think. All you need is the data validation feature and a formula.
You would like to get distinct values instead of preventing duplicates? Check out How to get distinct values in Excel? (6 ways).
Sample
What we would like to do
To prevent others from entering duplicate values in column A
Prevent duplicate entries with Data Validation rules
Step 1: Highlight the area where you need to avoid duplicates
Since I would like to avoid duplicates in employee ID, I would highlight the entire column A.
The data range do not have to be an entire column. It can be just several consecutive cells in the same column.
You may be interested in How To Center Cells Across Multiple Columns?
Step 2: Under Data tab, select Data Validation
Step 3: Choose custom from the allow drop down list
After doing this, the formula box should appear.
You may be interested in How To List All Worksheets Name In A Workbook
Step 4: Enter the formula and press OK
In the sample Excel, I entered the below formula.
=COUNTIF(A:A,A1)=1
Syntax
=COUNTIF(Cell ranges, the first cell in the cell ranges)=1
For example if your data range is C11 to C23, the formula will be like this:
=COUNTIF(C11:C23,C11)=1
Why does this formula work
COUNTIF function counts cells that match criteria. Take cell A2 (1004100) as an example. When 1004100 appears in column A for the first time, COUNTIF function would return “1”. When it appears for the second time, COUNTIF function would return “2”.
Since our data validation rules forces the result to be 1, a “2” will trigger the alert.
You may be interested in How to copy data from multiple sheets at once
Result
Congratulations! You can now try inputting a duplicate value and you should get an alert like this.
Hungry for more useful Excel tips like this? Subscribe to our newsletter to make sure you won’t miss out on any of our posts and get exclusive Excel tips!
