In this tutorial you will learn how to prevent duplicate entries in Excel columns by using Excel’s data validation feature.
This is useful if you have a column of data where certain columns cannot have data repeated. A good example of this would be driver’s license number, social insurance number and so on.
Here is the video of the tutorial. You can get the full written instructions below.
For our example, we will have a three column spreadsheet. User ID, Name, and Age. We do not want to allow duplicate entries in the User ID column.
In our first example, we will set it up so that duplicates are prevented throughout all of column A. To do that we must first highlight all of column A.
With that accomplished, we now go to the ribbon and click on the data tab. Then click on data validation.
The data validation window opens. In the Allow box, we choose custom. This will allow us to enter our formula.
In the Formula box, type in the following: =countif(a:a,a1)=1
Now click on the Error Alert tab. Enter the text as seen below into the Title, and Error Message boxes.
Now, when we enter a user ID, I will give two users the same ID and we’ll see what happens.
Let’s say you only want certain cells in a column set to prevent duplicates. Say A2 to A10. What we have to do is select those exact cells in step 1 and the formula is a tiny bit different.
In this case we use the exact cell range in which we want to prevent duplicates. Also, we have to use the absolute cell reference as shown by the $.
What exactly does that formula do? The countif function counts how many times the value entered in cell A2 appears in the range of cells from A2 to A20. The =1 means that each entry can appear a maximum of 1 time.
Because we highlighted all the cells from A2 to A20 when we entered our data validation, that means the formula is copied to all of those cells, except in cell A3 it becomes =countif($a$2:$a$20,A3)=1. The cell reference after the comma changes depending on which cell the formula is in. Because we used the $ to indicate an absolute cell reference in the range of cells from A2 to A20 that doesn’t change.