When processing data with Excel, sometimes duplicate data might lead to incorrect results or even serious consequences. We have already learned how to delete or merge the duplicate cells before, but it is actually possible to be prevented from the very beginning. This little trick demands Excel to auto alert you every time when entering duplicate data in a column.

1. First, select the entire column where you will continue to enter data.

2. Next, go to Data tab and click Date Validation in Data Tools group.

3. A Date Validation dialog box will display, go to Settings tab, select Custom in the box of Allow and enter the formula “=COUNTIF(A:A,A2)=1 in the box of Formula.

Note: For this example, I am entering the employee number into column A. I want to make sure all employee numbers in column A are unique and A2 is the data of first cell. You could change the values of formula according to actual condition.

4. Then, go to Error Alert tab, check the box of Show error alert after invalid data is entered, and set a title for it, such as Duplicate Data. You can also enter the detailed message in the Error message box. Simply click OK and finish it.

5. Now when you enter a duplicate number, Excel will prevent the entry and alert with an error message, it will show up like below. Click Retry and you can re-enter the number immediately.