Let’s see how to restrict user to enter a duplicate values in a column in excel.
There are several instances where we would like the user not enter any duplicate values in a column in excel for example cust_id column in excel will have only unique ids for customer.
We will see how to do that with the help of an example.
Step 1
Apply “Format as table” to your customer table data by selecting the data and selecting a format from Home->Format as Table tab in excel and press OK.
The reason behind formatting our data as table is that the will expand or shrink automatically based on addition or deletion of any records in the table and we do not need to take care of the dynamic range.
Step 2
We will create a name range by selecting the data in the CUST_ID column and typing the name in the selection bar in the top left corner as follows. Type enter when you have given the name in the box.
Our name range CUSTID has been created now.
Step 3
Select the data in column A and go to Data Validation under Data tab.
Step 4
Select Custom from Allow drop down and enter the formula =COUNTIF(CUSTID,A2)<=1 in the Formula box as shown in the fig.
CUSTID is the name range that we had created in step 2.
Step 5
Now hit tab to insert a row in the table by going at the last of table i.e. cell C5.
As soon as you hit the tab, a new record will be inserted.
Step 6
Now I will try to insert 4 as customer id and it will throw an error message and won’t let me enter 4 as customer id as the column already has the value 4 as customer id.
Hope this helped.
