Preventing Duplicates While Entering Data in Excel 2010

In this article we will learn about how to prevent duplicates while entering the data in Microsoft Excel 2010. We can use Countif formula to prevent the duplicates entry.
COUNTIF: - By using this formula you can identify the duplicate entries in a data and can capture duplicate entries in a range.

Syntax of “COUNTIF” function: =COUNTIF (range,criteria)

Example:-

We have table in range A2:A11, in which needs to count of states that how many times states are repeating in Column A.

Select the cell B2, and write the formula =Count if (A2:A11, A2) press enter.
The Count if Function will return 4, it means “Washington” is repeating 4 times in Column A.

Copy formula by pressing the key “CTRL+C” then select the range B3:B11 and paste the formula by pressing the key “CTRL+V”.
Select the range B2:B11 by using the key “CTRL + SHIFT + Down Arrow Key” & Copy by pressing the key “CTRL + C”, right click of the mouse select “PASTE SPECIAL”.
Paste Special dialog box will appearclick on “Value” then click on ok to convert the formula into text.

Let’s take an example to understand how and where we can prevent the duplicate entries.

We have a data of Mobile nos. In which we want to stop the duplicate entries of mobile numbers.

To preventing duplicates while entering in the data follow below given steps:-

Select the data range of Mobile number in the range B2:B10.
Go to “Data” tab and click on Data Validation from Data Tools group.
“Data Validation” dialog box will appear. In “Settings” tab select “Custom” from the Allow drop down list.
The formula tab will get active.
Write the formula in “Formula” box =COUNTIF(B:B,B1)=1

Go to “Error Alert” tab and type the message in “Alert box” , “Duplicate Entries are not allow” and click on ok.

To check the Data Validation is doing proper work or not enter the same mobile number in cell B11 which is in B10 cell.
After pressing the Enter key on your keyboard Error Alert message will get appear.

This is the way you can restrict the duplicate entries in a data by using the Data Validation option in Microsoft Excel 2010 and 2013.

SANDEEP KADAM
October 12, 2016 at 1:39 pm

HOW CAN I MANAGE WITH TWO COLUMN

Reply ↓
TRISH GONZALEZ
February 9, 2016 at 1:10 am

I FOLLOWED ALL THE STEPS AND FOR SOME UNKNOWN REASON IT DOES NOT WORK. I TRIED BOTH FORMULAS =COUNTIF(B:B,B1)=1 and =COUNTIF(B2:B500,B2)=1 THE SYSTEM STILL ALLOWS ME TO ENTER DUPLICATE ENTRIES.

PLEASE HELP!

Reply ↓
- Admin
  February 9, 2016 at 5:38 am
  
  Hi Trish,
  
  Make sure that the "Show error alert after invalid data is entered" is checked! If that is not checked, you would be able to enter duplicate entries.
  
  Also, in case you are trying to copy and paste the value from some other cell or drag the formula to the data validation cell, then too it will not prevent any duplicate entries. For that, we have to first protect the workbook.
  
  We hope it works for you know and in case if you still see any error, please feel free to write us back.
  
  Best Regards,
  Site Admin
  
  Reply ↓
ant
January 20, 2015 at 6:20 pm

TThe problem is that this works only if you input the numbers from the keyboard,if you copy past it dosent work

Reply ↓
Mike
August 23, 2013 at 8:30 am

"You need to type the message that is in the following brackets:
""The value you entered already appears in the list""
This is the message that will pop up when a duplicate is found. "

Reply ↓
Mike
August 23, 2013 at 8:30 am

"I am running Excel 2001 for the MAC.
I don't understand what I am supposed to put in the Error message Box. "" in the Error msg box enter the value you entered already appears in the list"" "

Reply ↓
Mike
August 23, 2013 at 8:29 am

"I found this tip very useful and I have shared this with all my colleagues.
Keep up the good work.
I am an excel enthusiast myself and would like to share my knowledge too."

Reply ↓
Mike
August 23, 2013 at 8:28 am

How do you sort the column with the duplicate error message? Can the column be sorted in ascending order? I want all the messages with duplicate to be together and same with the unique messages. I'm not getting that.

Reply ↓