I show you how to prevent duplicate values being entered into Excel using Data Validation.
Let's say we have a list of part numbers and we want to make sure that there are no duplicates, that a part number is not put into the list more than once.
First, select the range where you will be entering the list:
Make sure to select enough rows so that all future parts will be within this list. If you are in a version of Excel greater than 2007, you can simply select the entire column if you want.
Next, we need to create a formula to ensure no duplicates are allowed.
Here is the formula we need:
=COUNTIF($A$2:$A$15,A2)=1
There are two things you will need to change here to suite your needs, the reference to the list $A$2:$A$15 and the reference to the first cell in the list A2
$A$2:$A$15 is the location of the list where we don't want to allow duplicates. You MUST include the dollar signs $ in front of the column and row references for this to work correctly, this ensures this is an absolute cell reference, which means that it won't change.
A2 is the first cell of your list and it must NEVER include the dollar signs $ because it must always remain a relative cell reference, which means that it can update when copied to other cells.
Tip: When creating the formula, do it in the worksheet so you can make sure everything was typed correctly. When you put the formula into the Data Validation window, it will not check it for errors like Excel does when you create it in the worksheet.
Once you create your formula, you can then input it into Data Validation to prevent duplicate entries.
Remember to have the list where you don't want duplicates selected, then go to the Data tab and click the Data Validation button:
A window will open and you should select Custom from the Allow field:
Now, we can input our formula into the Formula field that will appear.
Now, hit the OK button and you are all done!
Let's test it out. When I try to enter asc-3 into cell A5, I get this error message:
I am now not able to enter a duplicate value into this list!
It's very important to select the entire range where you don't want duplicates before you apply Data Validation and it's very important that you leave the second range reference, in the case above A2, a relative cell reference, which just means to NOT put dollar signs in it.
When we now go to, for instance, cell A3 and click the Data Validation button, we see this:
Notice that everything in the formula has stayed the same EXCEPT that A2 has now become A3. This is why that cell reference being relative is so important and this is what allows us to prevent duplicate values in the list.
Also, if you want a custom error message or hint to tell a user not to enter duplicate values, you can edit the options on the Input Message and Error Alert tabs, both of which are quite self-explanatory.
As you can see, Data Validation in Excel allows you to do some pretty powerful things and it will go a long way to helping you keep your data from being corrupted.
Don't forget to download the accompanying Excel spreadsheet so you can see this feature in action and check out our other Data Validation tutorials to learn more about what you can do with it in Excel.