ArtsAutosBooksBusinessEducationEntertainmentFamilyFashionFoodGamesGenderHealthHolidaysHomeHubPagesPersonal FinancePetsPoliticsReligionSportsTechnologyTravel
  • »
  • Technology»
  • Computers & Software»
  • Computer Software

Data Validation to Prevent Duplicates in Excel

Updated on March 14, 2011

Data Validation can be used to perform some great things in Excel such as preventing spelling mistakes and typos, restricting date entries and preventing incorrect number entries.

This tutorial will look at using Data Validation to prevent duplicates in Excel. For the purposes of the tutorial we will prevent duplicate entries in the cell range A1:A500.

Prevent Duplicates in Excel using Data Validation

1.  Select cell range A1:A500

2.  Click the Data menu and select Validation in Excel 2003, or click the Data tab on the Ribbon and then click the Data Validation button in the Data Tools group if using Excel 2007 or 2010

3.  Select the Settings tab if necessary

3.  Click on the Allow list menu and select Custom. This will allow us to use a formula for the validation criteria

5.  In the Formula box type =COUNTIF($A$1:$A$500,A1)=1

Enter a formula in Data Validation to prevent duplicates in Excel
Enter a formula in Data Validation to prevent duplicates in Excel

6.  Click Ok

Try entering the same data twice in the cell range A1:A500. Excel will prevent this from happening and provide an error alert to inform you of what has gone wrong.

Unfortunately Excel error alerts are not very user friendly and most Excel users will not be aware that this message is telling them that they are entering a duplicate record.

Customise the Error Alert

Let’s look at customising the error alert to provide an easy to understand message to our users.

1.  Select cell range A1:A500

2.  Click the Data menu and select Validation in Excel 2003, or click the Data tab on the Ribbon and then click the Data Validation button in the Data Tools group if using Excel 2007 or 2010

3.  Select the Error Alert tab

4.  Enter a Title for the alert

5.  Enter the text you would like to appear in the Error message box

Create a customised error alert
Create a customised error alert

6. Click Ok

The new error alert will now be displayed the next time a user types a duplicate entry.

Check out these Excel video tutorials for more tips and tricks.

Comments

    0 of 8192 characters used
    Post Comment

    No comments yet.