Avoid Duplicate Entries Using Data Validation In Excel

Here is how you can use Data Validation to avoid entering duplicate data in a range.

You can download the file here and follow along. If you get a preview, look for the download arrow in the upper right hand corner.

I have a data range where I want to enter the 50 US states, and I want to make sure I don’t enter any duplicates. To accomplish this, we are going to use the COUNTIF function within Data Validation. COUNTIF “Counts the number of cells within a range that meets the given criteria.” The syntax is:


The range of data we want to validate for duplicates is D1:H10, so our formula will be:

=COUNTIF($D$1:$H$10,D1)< =1Notice how I've made the data range absolute cell references, while the criteria is a relative reference. In this way, as Data Validation checks each cell, it will compare it to the same overall data range.I've also set up a test range with this formula in it, and used Conditional Formatting to check if any of the cells register as FALSE:av4

Now, let’s enter the formula into Data Validation. First highlight the range D1:H10, then go to the Data tab and select Data Validation. Choose Custom, and enter our formula in the box provided and click OK:


Now, any duplicate entry will generate a warning message. Notice how it also generates a highlighted “FALSE” in our test range:


And there you go!

Happy Excelling!

Free Download!

Subscribe to Download Your FREE Copy of
"My 70+ Favorite Excel Keyboard Shortcuts" Today!

No comments yet.

Leave a Reply