Prevent Duplicate Entries In Excel

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.

Capture

Capture




Capture

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.

Capture

Our name range CUSTID has been created now.

Step 3

Select the data in column A and go to Data Validation under Data tab.

Capture

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.

Capture

Step 5

Now hit tab to insert a row in the table by going at the last of table i.e. cell C5.

Capture

As soon as you hit the tab, a new record will be inserted.

Capture

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.

Capture

Hope this helped.

Share The Knowledge

Random Posts

  • VBA To Open Latest Excel Workbook

    vba to open latest excel workbook in a folder, how to open most recent file from a folder in excel vba, open latest file from folder in excel vba, open most recent workbook in excel, code to open the most recent file from a folder in vba

    Share The Knowledge
  • Sort By Length In Excel

    In this article we will see how we could sort by length in excel i.e.We may have some data arranged in a […]

    Share The Knowledge
  • Create A Horizontal Scrolling Table In Excel

    Let’s understand how to create a horizontal scrolling table in excel as shown below. The data that is used to […]

    Share The Knowledge
  • Vlookup First Word In Excel

    In this post we will see how could we vlookup for first word in a string, here string could be […]

    Share The Knowledge

Leave a Reply