Take a Guess to Win Windows 10 Pro Key!
previous arrow
next arrow
Slider

How to Prevent Entering Duplicate Data in Microsoft Excel

Date:2019-8-27 Author:Olivia

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.

How to Prevent Entering Duplicate Data in Microsoft Excel

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

How to Prevent Entering Duplicate Data in Microsoft Excel

How to Prevent Entering Duplicate Data in Microsoft Excel

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.

How to Prevent Entering Duplicate Data in Microsoft Excel

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.

How to Prevent Entering Duplicate Data in Microsoft Excel

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.

How to Prevent Entering Duplicate Data in Microsoft Excel

Copyright Statement: Regarding all of the posts by this website, any copy or use shall get the written permission or authorization from Myofficetricks.

Leave a Reply

Your email address will not be published. Required fields are marked *