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:
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!