How to prevent duplicate data entries in Excel ? – Complete guide

April 25, 2022April 24, 2022 by Abhishek Pandey

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.

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.

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.

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.

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.

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.

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.

Prevent duplicate rows (record) entries in Excel

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?

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.

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.

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 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.

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?

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.

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.

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.

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]).

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.

Using Pivot table and minimal VBA

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.

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.

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.

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.

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.

In the below GIF you can see the user is not able to enter duplicate values at the same time only 10 digit mobile number is allowed.

The above data validation formula does not prevent users from entering non-numbers like “92233344ab”.

Since the mobile number is always numeric so there is a need to modify the data validation formula a little bit.

Select the range press ALT D L keys in series then replace previous formula with this =AND(COUNTIF($A$2:$A$9,$A2)=1,LEN($A2)=10,ISNUMBER($A2*1))

Click on the Ok button.

You may also like to read and learn from the below topics.

Concatenate and join cells in Excel and concatenate and join in VBA

How To Convert Number To Hindi Words in Excel?

How to insert page break in Excel? |Page break after value change

Leave a Comment Cancel reply