excel prevent duplicate entry

In this Ms. Excel article, you will learn various ways to prevent duplicate entries in Excel.

Here I have discussed various case scenarios like duplicate data prevention in a single column, and duplicate record (multiple columns) prevention.

You will also see how to stop duplicate data entry when data is copy-pasted instead of manual entry in a range.

Prevent duplicate entry in a column using data validation

In the below image you can see the name “Sonu” is entered twice but you want enter unique names only.

At the place of name, it can be anything like Mobile No. column, Invoice column.

Excel prevent duplicate entries in column

To prevent duplicate entries using data validation follow the below steps.

Select the column or range in which you want to make entry. In this example entry range is A2 to A7.
Make sure in selection first cells is Active.

Excel prevent duplicate entries with data validation

Keeping selection intact, go to the Data tab then click on Data Validation Option. Alternatively, you can press the ALT D L keys in series.

Data validation option location in Excel ribbon

In the Setting tab of the Data validation pane select Custom from Allow drop down list.
In formula input write =COUNTIF($A$2:$A$7,$A2)=1
Note:- In the above formula, it is important to make entry range reference absolute ($A$2:$A$7) and 1st cell of entry range ($A2) only column absolute, else data validation will not work properly.

Prevent duplicate entries with data validation in Excel

Optionally, you can write an input message of a data validation. This message will be shown when a cell is selected in the validated range.

excel data validation input message

In the Error Alter tab make sure “Show error alert after invalid data is entered” is ticked.
In Style, Stop is selected. Optionally, you can write a Title and Error message.
Click on the OK button to complete the data validation setting.

This message “Only unique values allowed” is shown when the wrong entry (duplicate entry) is done in the data validation range.

error alert in data validation

Now, if you manually enter a duplicate value then an error message will be thrown like the below image.

You need to press Enter key or click on the Retry button to change the value in the entered cell.

Note:- Data validation only works when each entry is done manually (by typing) only. In the case of copy-paste, data validation will not show any alert message when the entry is wrong.

duplicate value entry error alert excel data validation

Prevent duplicate rows (record) entries in Excel

https://youtu.be/doTMTQKF6uU

In the below image you can see the 3rd and 5th rows have the same entry.

Here, you will see how to prevent such duplicate records in Excel using Data Validation?

prevent duplicate rows records in Excel

1st in black column (here D column) create concatenate of all the entry values.
Remove all the spaces from concatenating value using the Substitute function.

Cell D2 formula =SUBSTITUTE(CONCATENATE(A2,B2,C2),” “,””)

Copy the formula up to the last row of entry (here up to the 6th row). Later after data validation, you can hide this formula column if you want.

prevent duplicate rows records in excel 2

Select the entry range (here A2:C6). Make sure 1st cell of selection is active.
Keeping selection intact press ALT D L keys in series to bring the Data Validation pane.
In Allow select custom and in formula write =COUNTIF($D$2:$D$6,$D2)=1
Optionally, you can write some message in the Error Alert tab.
Click on the Ok button.

prevent duplicate entries in excel

If you try to make a duplicate entry by typing then an error will be thrown list the below GIF.

This is how one can stop duplicate entries in multiple columns in Excel.

Prevent duplicate records entry in Excel

Prevent duplicate in Excel range using VBA

In the below GIF you can see when a duplicate record is entered at the 5th row then it gets deleted automatically.

But when the unique record is entered then it keeps the entry.

So you will how to prevent duplicate entry in a range using VBA.

Excel VBA to prevent duplicate entry in range

To insert the below code press ALT+F11 to open VBA IDE.
In Project explorer present on the left side double click on the sheet in which you want to make entries (in this example sheet is “eg3”). If project explorer is not visible then press CTRL+R.
Copy-paste the below code section in the open sheet module in VBA IDE.
Save as the workbook in either in binary (.xlsb) or in macro (.xlsm) format.

You may also like to read How to insert and run VBA code in Excel?

Excel VBA prevent to duplicate entry in range

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim entryRng As Range
  Set entryRng = Range("A2:B7") 'without heading
  If Application.Intersect(Target, entryRng) Is Nothing Then Exit Sub
  Application.EnableEvents = False
    entryRng.RemoveDuplicates Columns:=Array(1, 2), Header:=xlNo
  Application.EnableEvents = True
End Sub

For the above code to work, please ensure the event is enabled in Excel.

To enable event run Application.EnableEvents=True line in the immediate window if the event is disabled.

In the above code section you can modify “entryRng” as per your need at the same time please change Array(1,2) as per the number of columns in “entryRng”.

Prevent duplicate entries in Excel during copy paste

Excel data validation to prevent duplicate entries works only when entry is done manually or by typing.

But, it fails to prevent duplicate entries when data is copy-pasted from a range.

The below GIF clearly shows this pitfall of data validation.

Excel data validation prevent duplicate after copy paste

Use the below VBA code to prevent duplicate entries in Excel when copy-pasting.

Press ALT+F11 to open VBA IDE.
Double click on the sheet name (here sheet name is “eg1”) in the VBA IDE project explorer.
Copy-paste the below code in that opened sheet module.

Excel VBA prevent duplicate in range on copy paste

Private Sub Worksheet_Change(ByVal Target As Range)
  ''if copying is not active then exit code
  If Application.CutCopyMode = False Then Exit Sub
  Dim entryRng As Range
  Set entryRng = Range("A2:A7") 'without heading
  If Application.Intersect(Target, entryRng) Is Nothing Then Exit Sub
  Application.EnableEvents = False
    entryRng.RemoveDuplicates Columns:=Array(1), Header:=xlNo
  Application.EnableEvents = True
End Sub

In the above code section, you can modify “entryRng” as per your requirement, at the same time don’t miss to change Array() part.

Make sure the VBA event is enabled so that code runs automatically when copy-pasting is done.

The below GIF shows on copy-pasting duplicates it is keeping only unique records only.

Excel data validation prevent duplicate entry on copy paste

Prevent duplicate entry in Excel drop down list

Sometimes you may need to create a drop-down list from a table column having duplicate values, and this drop-down list should not contain duplicates.

Here I will show you two methods for this.

Using Unique function

One can use the Unique function (available in Office 365 only) to extract unique values of a column in a table.

1st select range with heading then press CTRL+T or Insert tab then click on Table. Make sure my Table has head is ticked and click on OK. This makes the range an official table.
In one blank cell (here G1) using unique function with input of a column range from table.
In this example formula looks like this =UNIQUE(Table1[Brand]).

prevent duplicate in Excel drop down list

Select one blank cell (here i1) then press ALT D L keys in series (Data Tab > Data validation) to bring the data validation pane.
In allow select List in sources write formula =OFFSET($G$1,,,COUNTA($G$1:$G$13),1)
Here $G$1:$G$13 is the max number of unique values that I expect when new records are added to the table, but you can increase the range rows as per your wish.
Click on the OK button.
Now when new records are added to the table, the unique values list will change automatically and the formula in data validation will auto-extend to the last row of the unique list.

prevent duplicate in Excel drop down list

Using Pivot table and minimal VBA

prevent duplicate in Excel drop down list using pivot table

If your Excel doesn’t support the Unique function then you can use this method.

Select range with heading then press CTRL+T to make it an official table
Go to the Insert tab then click on Pivot Table.
In Table/Range select a column with heading (here Brand column).
Choose where you want to place this pivot table whether in the New worksheet or the Existing worksheet ( here in the same sheet at G1)
Press the Ok button.

prevent duplicate values in Excel drop down list using pivot table

From PivotTable Fields place the Brand column (or tick brand) in the Rows section.
To remove Grand Total, select any cell of the pivot table then go to the Design tab then click on Grand Totals drop-down list then click on Off for Rows and Columns.

prevent duplicate values in Excel drop down list using pivot table-2

Select the blank cell (here I1) then apply list data validation and in the formula write =OFFSET($G$2,,,COUNTA($G$2:$G$13),1)
You can increase or decrease this range $G$2:$G$13 based on expected max unique values.

prevent duplicate values in Excel drop down list using pivot table-3

When new records are added pivot needs to be refreshed automatically for this copy-paste the below code in the sheet module in which the pivot is present.
In the above code “PivotTable1” in name of the pivot table, select any cell of the pivot table then go to PivotTable Analyze tab on the left most side you can see the name of the pivot table.

Make sure the VBA event is enabled so that code can run automatically when the table is updated.

Make sure to save Excel file in macro (.xlsm) or binary (.xlsb) format, else code will not be saved.

Private Sub Worksheet_Change(ByVal Target As Range)
 Dim entryRng As Range
 Set entryRng = Range("A1:D500")
 If Application.Intersect(Target, entryRng) Is Nothing Then Exit Sub
 Application.EnableEvents = False
    PivotTables("PivotTable1").PivotCache.Refresh
 Application.EnableEvents = True
End Sub

Restrict duplicate entry along with character length limit

In the below example we want to restrict duplicate mobile numbers entry along with mobile number must be of 10 digits.

Excel restrict duplicates along with character length limit

Select entry range (here A2 to A9). Make sure 1st cell in selection is active.
Keeping selection intact press ALT D L keys in series to bring the data validation pane.
In Allow select Custom and in formula write =AND(COUNTIF($A$2:$A$9,$A2)=1,LEN($A2)=10)
Click on the OK button.

Excel restrict duplicates along with character length limit 2