Data Validation to Prevent Duplicates in Excel

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.

More by this Author

  • Sort Pictures in an Excel List
    0

    If you are using pictures in your Excel spreadsheet you may be having some difficulty with sorting the pictures along with the other data in the list. The good news is that you can sort pictures in an Excel list just...

  • Create Star Wars Movie Credits in PowerPoint
    2

    This hub will explain how to create the scrolling text credits from the Star Wars movies in your PowerPoint presentation. To create the Star Wars credits we will use different animation effects simultaneously. ...

  • Brock Lesnar Nutrition and Workouts
    2

    Brock Lesnar is a phenomenon of a man and an athlete. Brock Lesnar weighs a lean 265 lbs and yet possesses extreme speed, agility and athleticism. How a man carrying so much muscle mass can maintain such high levels of...


No comments yet.

    Sign in or sign up and post using a HubPages Network account.

    0 of 8192 characters used
    Post Comment

    No HTML is allowed in comments, but URLs will be hyperlinked. Comments are not for promoting your articles or other sites.


    Click to Rate This Article
    working