How to avoid making duplications in a certain column in Excel?

* Data > Data Validation > Data Validation * in the Settings tab click drop down list under Allow, choose Custom, and then enter this formula “=COUNTIF($A$1:$A$1000,A1)=1” into the Formula box.
(depend in what column and till what row).
*click Error Alert tab, under the Title box, enter “Duplicate Entry”, and enter the proper message in the Error message box.
By this way we prevent duplication just after invalid data is entered

 

Select required cells.

 

1. Select conditional formatting.

 

2. Goto highlight cells rule.

 

3. Select duplicate value.

 

Duplicate values will be highlighted in the selected cells.

 

 

Step by Step, usually I use :

1. create new column in the left, ex in column A, named FLAG or COUNT
2. in row2, give formula "=COUNTIF(B:B,B2)"
3. you can create filter (Menu DATA >> FILTER)
4. copy to all row in column A
5. you can monitoring the result, if it show "2" means, duplication

more tip trik you can visit macro excel - tutorial by irajimmy bekatul

Adding to the above, we may use data>subtotal> and count at each change

 

1 - Settings tab 2 - drop down list 3 - choose Custom 4 - enter formula “=COUNTIF($A$1:$A$1000,A1)=1” 5 - in the Formula box. *click Error Alert tab 6 - enter “Duplicate Entry”, 7 - enter message. Follow these steps to prevent duplication

Sort Your List, select it , choose option Remove Duplicates from Data Tab, select column name and press OK button all duplicate values will be removed by Excel

the conditional format is the easiest way to find the duplication in a certain column

I always prefer Conditional formatting to highlight duplicates.This is technique i was advised to use on one the courses i attended.Its used by auditors extensively and also by internal auditors.

If you are entering in one column without any blanks, auto complete will show you if there is duplicate. Or you can use conditional formating Or Countif function Or remove duplicates Or you can make a macro which includes one / all the above to cut the steps to one keyboard shortcut only

Any of the above is correct and is depend on the format  type of your column or row (text , numeric or date ), then you can choose the suitable one. by the way conditional format is suitable for both text and date formats.

Apply conditional formating in a column which will alert you for duplicates entry each time.