Excel Tip: How to Prevent Data Duplication In an Excel Worksheet

Data duplication in Microsoft® Excel® can lead to a host of irregularities; so it is a good idea to prevent it. In this tip we explain how you can prevent duplicate data from being captured in an Excel worksheet by using custom data validation The COUNTIF function will be added to data validation the option to prevent duplicate data entries.

A key benefit of using this method is that it prevents data duplication before it is captured. Other methods will only allow you to highlight and remove duplicate values after your data has been captured.

Example exercise – How to prevent the entry of duplicate account codes.

Note: Download the sample workbook to practice this exercise.

Applies to: Microsoft® Excel® 2007, 2010 and 2013

The screen shot below is used in this example:

1 15 2014 3 55 37 PM


1. Select cells A3:A15

2. Select Data Validation as per the screen shot below.

1 15 2014 4 04 07 PM


3. Enter the data in the same format as shown below.

1 15 2014 4 05 06 PM


4. To add an input message select the Input Message tab and enter the appropriate message.

1 15 2014 4 05 54 PM


5. To add a custom error message, select the Error Alert tab and enter the appropriate message.

1 15 2014 4 06 35 PM

6. Click OK

7. Select cell A3 and enter the account code “ACC001”.

8. Select cell A4 and re-enter the same account code “ACC001”.

9. The custom error message, entered in point 5, will be displayed.

10. Select Retry and enter the account code “ACC002”.

11. The value will be accepted as shown below.





Now get out there and brag about your new fancy Excel skills!


For more Excel Tips, subscibe to the Equation Technolioges Blog!


Need Help Now?

Request a Call